Oracle: Decoding and Subquery Result

Question

I have an oracle query, and part of it calculates 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" is computed at runtime, and these are rather complex joins - several tables, use PARTITION BY , etc. Therefore, I want to avoid executing the same subquery if the value is not equal to "0". Is there a way to write something like this

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

Answer 1

Will this work for you? I just moved "23" to the 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 ... 

The CASE statement can 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 ... 

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

Allows the use of the q query during the main selection, where a subquery is ever allowed. A WITH clause or Common Table expression or factoring subquery is called. Read more about this at Oracle-Base.com .

Answer 3

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

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

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

Answer 4

You can use the subquery from the sentence 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 ) 

Answer 5

You better use the CASE statement. since the CASE statement is similar to a series of IF statements using only the WHEN keyword. The CASE statement is evaluated top to bottom. If the condition is true, then the corresponding THEN clause is executed, and execution proceeds to the END CASE clause (short circuit assessment).