DECODE versus CASE, continued

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.