CODEWITHSUNDEEP
sqloracle-databasecasecase-statement
Kasia Wichrowska
Kasia Wichrowska

Reputation: 321

Oracle Case Statement when x is null then else returns null

I have written a simple case statement as below:

case when col_name is null then 'NO' else 'YES' end as col_name

But I'm still getting (null) instead of a 'NO' ? Any ideas?

I have tried following still no luck:

case when isnull(col_name, 0) = 0 then 'NO' else 'YES' end as col_name

I have also tried this:

case when nvl(col_name, 0) = 0 then 'NO' else 'YES' end end as col_name

I know that NULL is never equal to NULL - NULL is the absence of a value. NULL is also never not equal to NULL.

Whole query looks something like this:

Select 

t1.col_a,
t1.col_b,
t1.col_c,
.
.
t2.col_a
t2.col_b

from table_1 t1
 left join table_2 t2 
  on t1.col_a = t2.col_a

 left join
 (select distinct

  case when t3.col_name is null then 'NO' else 'YES' end as col_name,
  t3.col_a,
  t3.col_b,
  t3.col_c) ABC

on abc.col_a = t2.col_a

Upvotes: 0

Views: 3534

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The LEFT JOIN is returning NULL, not the CASE. Return the column name and do the case logic in the outermost query:

select . . . ,
       (case when t2.column_name is null then 'NO' else 'YES' end)

Upvotes: 2

Related Questions