oracle: decode and subquery select result

Question

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 ...

Solution 1

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
  ...

Solution 2

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.

Solution 3

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.

Comments

[hanumant]

Hi @DCookie, Thanks for your input. I cannot use 'with as' since the subquery is using some values from outer query.