This example illustrates the advantage of CASE over DECODE...
The next example illustrates the advantage of CASE over DECODE. It computes shipping charges based on a subtotal, according to these rules
- Subtotal under $10.00: Shipping is $3.00
- $10.00 to $19.99: $5.50
- $20.00 to $99.99: $7.50
- $100.00 or more: 7% of subtotal (10% outside US)
SELECT subtotal, country, DECODE ( SIGN (subtotal - 20.00), -1, DECODE ( SIGN (subtotal - 10.00), -1, 3.00, 5.50 ), DECODE ( SIGN (subtotal - 100.00), -1, 7.50, DECODE ( country, 'US', .07, .10 ) * subtotal ) ) AS shipping FROM order ... ;
SELECT subtotal, country, CASE WHEN subtotal < 10.00 THEN 3.00 WHEN subtotal < 20.00 THEN 5.50 WHEN subtotal < 100.00 THEN 7.50 WHEN country = 'US' THEN subtotal * .07 ELSE subtotal * .10 END AS shipping FROM order ... ;
CASE, like DECODE, works from left to right: it evaluates the WHEN-conditions in order, and as soon as it finds one that is true, it returns the corresponding THEN-value and quits. For example, in computing the shipping charges above, suppose the subtotal is 15.00. CASE will first test to see if subtotal is less than 10.00. It isn't, so CASE will move on to the next condition, and test if subtotal is less than 20.00. It is, so CASE immediately returns 5.50: it does not perform any of the remaining tests.
Anything you can do with CASE can be done in DECODE, but it's likely to be tortuous. CASE is almost always easier to read and understand, and therefore it's easier to debug and maintain.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.