Q
Problem solve Get help with specific problems with your technologies, process and projects.

DECODE versus CASE, continued

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)
DECODE Searched CASE
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.


This was last published in August 2003

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close