Re: Differences between DECODE and 9i's CASE

From: Joel Meulenberg <joelme_at_attbi.com>
Date: 13 Mar 2003 17:49:47 -0800
Message-ID: <14ecab3f.0303131749.246543c6_at_posting.google.com>

dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0302131314.5398a82a_at_posting.google.com>...
> "Gorm" <andreas_at_spiqu.com> wrote in message news:<eRN2a.10600$FF4.629793_at_newsb.telia.net>...
> > The CASE expression can NOT return NULL, which DECODE can!
> >
>
> Actually it can,
>
> SQL> select case when 1=2 then 1 else null end from dual;
>
> CASEWHEN1=2THEN1ELSENULLEND
> ---------------------------
>
>
> 1 row selected.

There are, however, differences in datatype coversion for NULLs in DECODE and CASE expressions. Check out these query results:

  1 SELECT

  2    max(b) "plain max"
  3   ,max(decode(b,999,NULL,b)) "max of decode"
  4   ,max(CASE WHEN b = 999 THEN NULL ELSE b END)  "max of case"

  5 FROM
  6 (SELECT 2 b FROM dual
  7 UNION ALL
  8* SELECT 100 b FROM dual)
SQL> /  

 plain max max of decode                            max of case
---------- ---------------------------------------- -----------
       100 2                                                100

  1 SELECT

  2    max(b) "plain max"
  3   ,max(decode(b,999,NULL,b)) "max of decode"
  4   ,max(CASE WHEN b = 999 THEN NULL ELSE b END)  "max of case"

  5 FROM
  6 (SELECT 3 b FROM dual
  7 UNION ALL
  8* SELECT 100 b FROM dual)
SQL> /  

 plain max max of decode                            max of case
---------- ---------------------------------------- -----------
       100 3                                                100

  1 SELECT

  2    max(b) "plain max"
  3   ,max(decode(b,999,NULL,b)) "max of decode"
  4   ,max(CASE WHEN b = 999 THEN NULL ELSE b END)  "max of case"

  5 FROM
  6 (SELECT 1 b FROM dual
  7 UNION ALL
  8* SELECT 100 b FROM dual)
SQL> /  

 plain max max of decode                            max of case
---------- ---------------------------------------- -----------
       100 100                                              100

Actually, I'd been running such queries in SQL-Station, and scratching my head over what might be going on. However, when I ran them in SQL*Plus, I finally got a clue. Notice that the "max of decode" is left-justified while the other two columns are right-justified. SQL*Plus justifies data according to datatype. It believes that the "max of decode" is a *character* value, while the other two are numeric values. By wrapping the "NULL" in a TO_NUMBER conversion, the problem goes away:

  1 SELECT

  2    max(b) "plain max"
  3   ,max(decode(b,999,TO_NUMBER(NULL),b)) "max of decode"
  4   ,max(CASE WHEN b = 999 THEN NULL ELSE b END)  "max of case"

  5 FROM
  6 (SELECT 2 b FROM dual
  7 UNION ALL
  8* SELECT 100 b FROM dual)
SQL> /    plain max max of decode max of case
---------- ------------- -----------

       100 100 100

What I find really annoying is that the DECODE expression and the CASE expression should be equivalent.
Note that this problem affects date values as well as numeric values.

  1 SELECT
  2 max(b) "plain max"
  3 ,max(decode(b,TO_DATE('01010001','MMDDYYYY'), NULL,b)) "max of decode"
  4 ,max(CASE WHEN b = TO_DATE('01010001','MMDDYYYY') THEN NULL ELSE b END) "
max of case"
  5 FROM
  6 (SELECT to_date('01012003','MMDDYYYY') b FROM dual   7 UNION ALL
  8* SELECT to_date('12312002','MMDDYYYY') b FROM dual) SQL> /   plain max max of deco max of case
----------- ----------- -----------
01-JAN-2003 31-DEC-2002 01-JAN-2003   1 SELECT
  2 max(b) "plain max"
  3 ,max(decode(b,TO_DATE('01010001','MMDDYYYY'), TO_DATE(NULL),b)) "max of de
code"
  4 ,max(CASE WHEN b = TO_DATE('01010001','MMDDYYYY') THEN NULL ELSE b END) "
max of case"
  5 FROM
  6 (SELECT to_date('01012003','MMDDYYYY') b FROM dual   7 UNION ALL
  8* SELECT to_date('12312002','MMDDYYYY') b FROM dual) SQL> /   plain max max of deco max of case
----------- ----------- -----------
01-JAN-2003 01-JAN-2003 01-JAN-2003 +Joel Meulenberg Received on Fri Mar 14 2003 - 02:49:47 CET