Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Null values and Switch CASE - Conflict !!!

551792Dec 14 2006 — edited Dec 14 2006
Hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 11 2007
Added on Dec 14 2006
4 comments
937 views