Null values and Switch CASE - Conflict !!!
551792Dec 14 2006 — edited Dec 14 2006Hi I am using the following statements:
SELECT NUM,NAME,
CASE NAME
WHEN '0' THEN 'zero'
WHEN NULL then 'not defined'
ELSE 'defined' END
FROM TEST1 ;
Table data:
The table test1 contains only 1 row with NAME as NULL, but still the Switch case statement cannot recognize NULL and prints defined.
i.e.
NUM NAME CASENAMEWHEN0THENZEROWHENNULLTHENNOTDEFINEDELSEDEFINEDEND
---------------------- ---------------------------------------- ---------------------------------------------------------
2 defined
that means the NULL is not recognized by WHEN NULL Then....
Please suggest what is way out or theres a syntaxt mistake.
one way is to use NVL with NAME, but dont want to use it.
i am using Oracle 10g
Thanks