DECODE is not a simplified form of CASE
-
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!
-
Weird!
select case when null is null then 0 else 1 end
from dual;
produces the expected result.
select case when null = null then 0 else 1 end
from dual;
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
from
(
select 1 as A, null as B
from dual
union
select 2 as A, sysdate as B
from dual
) MyCrap
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?
-
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm#i1017437
"In a
DECODE
function, Oracle considers two nulls to be equivalent. Ifexpr
is null, then Oracle returns theresult
of the firstsearch
that 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!
-
Work-around for this "feature"
1 select case nvl(NULL,'X') when 'X' then 'Null-Test' else 'NOTnull-test' end
2* from dual
CASENVL(N
---------
Null-Test1 select case nvl('data','X') when 'X' then 'Null-Test' else 'NOTnull-test' end
2* from dual
CASENVL('DAT
------------
NOTnull-test
-
<font size="7">RISE FROM YOUR GRAVE!</font>