Re: wats the difference b/w case and decode

vannalas_at_gmail.com schrieb:
> Hi all
>
> What's the difference between the case and decode functions in oracle.
>
> Thanks in advance.
>

The short answer - there is practically no difference. The slightly longer one:
1) CASE is ANSI compliant, decode is proprietary 2) CASE is longer to write, but easier to read - with decode you write very compact
3) They have different behaviour by handling NULL's - so the same logic coded with decode and case yield different results

SQL> with t as (

   2 select 1 id,2 id2,null val from dual)    3 select decode(val,null,id,id2) decode_val,    4 case val when null then id else id2 end case_val    5 from t
   6 /

DECODE_VAL CASE_VAL
---------- ----------

          1 2

Best regards

Maxim Received on Tue Mar 06 2007 - 02:40:52 CST