CODEWITHSUNDEEP
sqloracle-database
NoName123
NoName123

Reputation: 137

Oracle: Handling Null In Case Statement

I have a huge query used within a case-when block. My query looks like this:

SELECT 'TEST' FROM DUAL WHERE 1=1 AND EXISTS(
SELECT 
  CASE
   WHEN EXISTS
     (Select 1 from dual where 1=2)
   THEN 1
   ELSE 
    (Select 1 from dual where 1=2)
   END
FROM DUAL);

I want to execute my select-statement only if the case-when statement returns a record. However, it always prints 'Test' because this code always return a NULL:

SELECT 
  CASE
   WHEN EXISTS
     (Select 1 from dual where 1=2)
   THEN 1
   ELSE 
    (Select 1 from dual where 1=2)
   END

So basically I want to print "TEST" only if no record (or null value) is returned. How can I achieve this?

Upvotes: 0

Views: 4367

Answers (2)

Chhaya Sethi
Chhaya Sethi

Reputation: 1

We can use the following to handle the NULL

CASE TRIM(Your Field) IS NULL
THEN 'The value you want to show/ print'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

A row with one column that has a NULL value is not the same as a non-existing row. So, you cannot do exactly what you want using EXISTS. One method is to do:

SELECT 'TEST'
FROM DUAL
WHERE 1 = 1 AND
      1 = (SELECT CASE WHEN EXISTS(Select 1 from dual where 1=2)
                       THEN 1
                       ELSE 0
                  END
           FROM DUAL
          );

That is, look for a particular value, rather than check for the existence of a row.

Upvotes: 2

Related Questions