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. If expr is null, then Oracle returns the result of the first search 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-Test

     

      1  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>


Log in to reply