CODEWITHSUNDEEP
sqloracle-databasesubquerydecode
hanumant
hanumant

Reputation: 1101

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

Upvotes: 7

Views: 56604

Answers (5)

janasainik
janasainik

Reputation: 831

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.

Upvotes: 0

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

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.

Upvotes: 1

DCookie
DCookie

Reputation: 43533

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.

Upvotes: 1

Yusuf Soysal
Yusuf Soysal

Reputation: 177

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 )

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58685

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

Upvotes: 10

Related Questions