oracle: decode and subquery select result

I have a oracle query and part of it is calculating some value using DECODE. For example:

SELECT ..., 
      (SELECT DECODE((SELECT 23 FROM DUAL), 
                      0, null,
                     (SELECT 23 FROM DUAL))  
         FROM DUAL) 
  FROM ...
开发者_运维百科

Here the value "23" gets calculated at runtime, and it's quite complicated joins - multiple tables, uses PARTITION BY etc. So I want to avoid executing the same subquery if the value is not "0". Is there any way to write something like this

SELECT ..., 
       (SELECT DECODE ((SELECT 23 FROM DUAL) as test, 
                        0, null,
                       test)  
         FROM DUAL) 
  FROM ...

Will this work for you? I've just moved the "23" to an inline table with a descriptive alias.

select ..., 
  (
   select 
     decode ( 
            computed_value.val, 
            0, null,
            computed_value.val
            )  
   from
     (select 23 as val from dual) computed_value
  )
from
  ...

A CASE statement might also add clarity, as in:

select
  ...
 ,case when computed_value.val = 0
       then null
       else computed_value.val
       end as my_field
from
  (select 23 as val from dual) computed_value
  ...

Or:

WITH q AS (
SELECT 23 test, 16 test2 FROM dual
)
SELECT ... 
     , DECODE(q.test, 0, NULL, q.test) value
     , CASE WHEN q.test2 = 0 THEN NULL 
            WHEN q.test2 = 16 THEN 1
            ELSE q.test2
       END another_value
  FROM q, ...

Lets you use the query "q" throughout your main select, where ever a subquery is allowed. Called the WITH clause, or Common Table Expression, or Subquery Factoring. Read more about it at Oracle-Base.com.

For this particular scenario, you could use the NULLIF function:

SELECT ..., 
      (SELECT NULLIF((SELECT 23 FROM DUAL), 0)  
         FROM DUAL) 
  FROM ...

The NULLIF function returns NULL if the two arguments are equal, otherwise it returns the first argument.

You can use the subquery in from clause and do something like below:

select conf_key, decode(test, 0, null, test) from (
select conf_key, (select conf_value from config_values where conf_key = 'DOMAINID') as TEST from config_values )

Better you would have use CASE statement. since the CASE statement is like a series of IF statements, only using the key word WHEN. A CASE statement is evaluated from top to bottom. If a condition is true, then corresponding THEN clause is executed and execution jumps to the END CASE (short circuit evaluation) clause.