DECODE is not a simplified form of CASE
SQB last edited by
Again, according to Oracle doc they're the same. Testing shows that DECODE regards NULLS as equal, while CASE (and all other statements involving NULL) do not.
SELECT CASE NULL WHEN NULL THEN 0 ELSE 1 END AS test FROM dual;
SELECT DECODE(NULL, NULL, 0, 1) AS test FROM DUAL;
The first returns 1, since NULLS aren't equal, the second returns 0 because to DECODE, they are.
Whether NULLS are equal can be debated, but once you make your choice, stick ot it!
select case when null is null then 0 else 1 end
produces the expected result.
select case when null = null then 0 else 1 end
Produces the result you have, but then null can not ever equal anything even null but can be tested to see if it is null as in my example. Seems your example defaults to an equality test i.e. my second example. Tis not intuitive but perhaps correct? Need to read up on the SQL case standard, what is the actual expected result?
Decode is crap,*10 when nested.
select A, Case B when null then 'Legs' else 'Bum' end
select 1 as A, null as B
select 2 as A, sysdate as B
This use of case is certainly doing an equality check. This is odd decode can only ever do equality checks but must make a special case for nulls.
I recon the case statement is working as expected and that as decode is just a strange oracle function is also working expected, Im sure the documentation makes it clear that nulls pass equality checks in decodes?
DECODEfunction, Oracle considers two nulls to be equivalent. If
expris null, then Oracle returns the
resultof the first
searchthat is also null."
Oracles documentation for the decode statement makes it clear that null can equal null, it is a special case, your examples return the results expected.
The fact that Oracle is inconsistent is a wtf, but not their documentation!
JRex last edited by
Work-around for this "feature"
1 select case nvl(NULL,'X') when 'X' then 'Null-Test' else 'NOTnull-test' end
2* from dual
1 select case nvl('data','X') when 'X' then 'Null-Test' else 'NOTnull-test' end
2* from dual
belgariontheking last edited by
<font size="7">RISE FROM YOUR GRAVE!</font>