Home » SQL & PL/SQL » SQL & PL/SQL » Case statement with null in where clause (oracle 10g, Toad, xp)
Case statement with null in where clause [message #611215] Sun, 30 March 2014 11:22 Go to next message
Crimzon
Messages: 3
Registered: March 2014
Location: Dubai
Junior Member
Hi,

I have following query but i do not know how to handle null in case statement of where clause,

Gucci column has 100 rows and 40 rows are null and Adidas filled with all 100 rows.
I want from user on parameter form to select their desired parameter for Gucci column as follow but it does not work. kindly help me out.
select gucci, adidas
from sports
WHERE  gucci = (case when :userinput = '0'  -- to select all row in gucci column
                      then gucci
                     when :userinput = '1'  -- to return only null 40 null rows in gucci column
                      then Null 
                     when :userinput = '2'  -- to return 60 data rows in gucci column
                      then is not null
                END );


[EDITED by LF: cleaning up]

[Updated on: Sun, 30 March 2014 15:18] by Moderator

Report message to a moderator

Re: Case statement with null in where clause [message #611218 is a reply to message #611215] Sun, 30 March 2014 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: Case statement with null in where clause [message #611219 is a reply to message #611215] Sun, 30 March 2014 12:07 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
case when :userinput = '0' -- to select all row in gucci column
then gucci

is, in general incorrect, if column gucci can have NULL values (and it does in your case) since NULL = NULL is always UNKNOWN and not TRUE. Anyway, there is almost never a need to use CASE in WHERE clause:

select  gucci,
        adidas
  from  sports
  where :userinput = '0'  -- to select all row in gucci column
     or (
             :userinput = '1'  -- to return 60 data rows in gucci column
         and
             gucci is null
        )
     or (
             :userinput = '2'  -- to return only null 40 null rows in gucci column
         and
             gucci is not null
        )
/


SY.

[Updated on: Sun, 30 March 2014 12:07]

Report message to a moderator

Previous Topic: How to store GMT timestamp in Database from SQL Developer import excelsheet
Next Topic: 30 minute Time Interval
Goto Forum:
  


Current Time: Fri Mar 29 04:52:03 CDT 2024