Re: wats the difference b/w case and decode

"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news:45ED2914.6020304_at_gmail.com...
> 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

CASE can handle complex expressions that DECODE cannot.

-- 
Terry Dykstra