Case statement with null in where clause [message #611215] |
Sun, 30 March 2014 11:22 |
|
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 #611219 is a reply to message #611215] |
Sun, 30 March 2014 12:07 |
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
|
|
|