Re: Diff bet'n DECODE and CASE stmt

From: Dave <davidr21_at_hotmail.com>
Date: 11 Feb 2004 14:10:01 -0800
Message-ID: <8244b794.0402111410.cd0143c@posting.google.com>

rohitk1973_at_yahoo.com (rohit) wrote in message news:<74353ecc.0402110748.4525e477_at_posting.google.com>...
> What is the difference between Decode and Case statements when used in
> a SQL query? The two appear to do the same IF-THEN-ELSE logic, I am
> not sure how the optimizer treats the two. Any explanation on how the
> optimizer uses the two would be super.
>
> Mucho thanks,
> Rohit

I don't know all the differences (like if there are performance diffs) but the CASE statement is more flexible/powerful. DECODE only does equality tests on one column I think, CASE can evaluate more complex logic statements.

For instance,

CASE WHEN T1.COl1 > 2 THEN 'X'

     WHEN T1.COL2 IS NULL THEN 'Y'
     WHEN T99.COL1 != T1.COL2 THEN 'Z'
     WHEN T1.COL1 = 3 AND T1.COL2 = 3 THEN 'J'
     ELSE 'Q'

END Dave Received on Wed Feb 11 2004 - 16:10:01 CST