Mega Code Archive
Using the CASE Expression
<![CDATA[
CASE expression perform if-then-else logic in SQL without having to use PL/SQL.
CASE works in a similar manner to DECODE().
CASE is ANSI-compliant.
There are two types of CASE expressions:
Simple case expressions use expressions to determine the returned value.
Searched case expressions use conditions to determine the returned value.
Simple CASE expressions use expressions to determine the returned value and have the following syntax:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
where
search_expression is the expression to be evaluated.
expression1, expression2, ..., expressionN are the expressions to be evaluated against search_expression.
result1, result2, ..., resultN are the returned results (one for each possible expression). If expression1 evaluates to search_expression, result1 is returned, and so on.
default_result is the default result returned when no matching expression is found.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
SQL>
SQL> CREATE TABLE book(
2 title_id CHAR(3) NOT NULL,
3 title_name VARCHAR(40) NOT NULL,
4 type VARCHAR(10) NULL ,
5 pub_id CHAR(3) NOT NULL,
6 pages INTEGER NULL ,
7 price DECIMAL(5,2) NULL ,
8 sales INTEGER NULL ,
9 pubdate DATE NULL ,
10 contract SMALLINT NOT NULL
11 );
Table created.
SQL>
SQL> INSERT INTO book VALUES('T01','Java','history','P01',111,21.99,566,DATE '2000-08-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T02','Oracle','history','P03', 114,19.95,9566,DATE '1998-04-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T03','SQL','computer','P02', 122,39.95,25667,DATE '2000-09-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T04','C++','psychology','P04', 511,12.99,13001,DATE '1999-05-31',1);
1 row created.
SQL> INSERT INTO book VALUES('T05','Python','psychology','P04', 101,6.95,201440,DATE '2001-01-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T06','JavaScript','biography','P01', 173,19.95,11320,DATE '2000-07-31',1);
1 row created.
SQL> INSERT INTO book VALUES('T07','LINQ','biography','P03', 331,23.95,1500200,DATE '1999-10-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T08','C#','children','P04', 861,10.00,4095,DATE '2001-06-01',1);
1 row created.
SQL> INSERT INTO book VALUES('T09','SQL Server','children','P04', 212,13.95,5000,DATE '2002-05-31',1);
1 row created.
SQL> INSERT INTO book VALUES('T10','AJAX','biography','P01', NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES('T11','VB','psychology','P04', 821,7.99,94123,DATE '2000-11-30',1);
1 row created.
SQL> INSERT INTO book VALUES('T12','Office','biography','P01', 507,12.99,100001,DATE '2000-08-31',1);
1 row created.
SQL> INSERT INTO book VALUES('T13','VBA','history','P03', 812,29.99,10467,DATE '1999-05-31',1);
1 row created.
SQL>
SQL> SELECT
2 title_id,
3 type,
4 price,
5 CASE type
6 WHEN 'history'
7 THEN price * 1.10
8 WHEN 'psychology'
9 THEN price * 1.20
10 ELSE price
11 END
12 AS "New price"
13 FROM book
14 ORDER BY type ASC, title_id ASC;
TIT TYPE PRICE New price
--- ---------- ---------- ----------
T06 biography 19.95 19.95
T07 biography 23.95 23.95
T10 biography
T12 biography 12.99 12.99
T08 children 10 10
T09 children 13.95 13.95
T03 computer 39.95 39.95
T01 history 21.99 24.189
T02 history 19.95 21.945
T13 history 29.99 32.989
T04 psychology 12.99 15.588
TIT TYPE PRICE New price
--- ---------- ---------- ----------
T05 psychology 6.95 8.34
T11 psychology 7.99 9.588
13 rows selected.
SQL>
SQL> drop table book;
Table dropped.
SQL>
]]>