CODEWITHSUNDEEP
sqloracle-database
Joe
Joe

Reputation: 103

Select case when null Oracle

I am trying to use a case statement to return a value when either the data is null or the value is 99. Is this possible with oracle? Below is my code which does not work because is null and null throw an error. How can I have the sql statement return Y when the value is either NULL or 99 and N when the value is anything else?

SELECT CASE StudentStatus 
         WHEN '99' THEN 'Y' 
         WHEN IS NULL THEN 'Y' 
         ELSE 'N' 
     END From USERS

Thanks for your help!

Upvotes: 0

Views: 1941

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use nvl() function within decode() as :

 select decode(
               nvl(StudentStatus,99),
                                     99,'Y','N') as status
   from users

Demo

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

You could rewrite it using DECODE:

SELECT DECODE(StudentStatus, '99', 'Y', '', 'Y', 'N')
FROM Users

db<>fiddle demo

Output:

+----------------+-------------------------------------------+
| STUDENTSTATUS  | DECODE(STUDENTSTATUS,'99','Y','','Y','N') |
+----------------+-------------------------------------------+
|            99  | Y                                         |
|                | Y                                         |
|           100  | N                                         |
+----------------+-------------------------------------------+

Upvotes: 1

jarlh
jarlh

Reputation: 44795

Do it the other way, let your case expression check when StudentStatus <> '99':

SELECT CASE when StudentStatus <> '99' then 'N'
         ELSE 'Y' 
       END
From USERS

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133400

try using 

SELECT CASE WHEN ( StudentStatus  = '99' OR StudentStatus IS NULL ) THEN 'Y' 
       ELSE 'N' END 
From USERS

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

SELECT CASE 
         WHEN StudentStatus  = '99' THEN 'Y' 
         WHEN StudentStatus IS NULL THEN 'Y' 
         ELSE 'N' 
       END 
From USERS

OR

SELECT CASE 
         WHEN StudentStatus  = '99' OR StudentStatus IS NULL 
         THEN 'Y'              
         ELSE 'N' 
       END 
From USERS

Upvotes: 2

Related Questions