i




 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Sql CASE Statement
12-29-2010, 11:24 AM
Post: #1
Sql CASE Statement
CASE is used to provide if-then-else type of logic to SQL. Its syntax is:

Code:
SELECT CASE ("column_name")
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name"

"condition" can be a static value or an expression. The ELSE clause is optional.

In our Table Store_Information example,

Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999

if we want to multiply the sales amount from 'Los Angeles' by 2 and the sales amount from 'San Diego' by 1.5, we key in,
Code:
SELECT store_name, CASE store_name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Date
FROM Store_Information

"New Sales" is the name given to the column with the CASE statement.

Result:
store_name New Sales Date
Los Angeles $3000 Jan-05-1999
San Diego $375 Jan-07-1999
San Francisco $300 Jan-08-1999
Boston $700 Jan-08-1999



Oracle Certified Trainer
[Image: signature.php?uid=40]
Quote


Possibly Related Threads...
Thread: Author Replies Views: Last Post
  Statement Handles or Cursors Usman 0 2,065 01-01-2011 07:50 AM
Last Post: Usman
  Delete From Statement Usman 0 1,892 12-29-2010 11:18 AM
Last Post: Usman



User(s) browsing this thread: 1 Guest(s)