Using the CASE Expression : CASE « Query Select

Using the CASE Expression : CASE « Query Select « Oracle PL/SQL Tutorial

1.	Introduction
2.	Query Select
3.	Set
4.	Insert Update Delete
5.	Sequences
6.	Table
7.	Table Joins
8.	View
9.	Index
10.	SQL Data Types
11.	Character String Functions
12.	Aggregate Functions
13.	Date Timestamp Functions
14.	Numerical Math Functions
15.	Conversion Functions
16.	Analytical Functions
17.	Miscellaneous Functions
18.	Regular Expressions Functions
19.	Statistical Functions
20.	Linear Regression Functions
21.	PL SQL Data Types
22.	PL SQL Statements
23.	PL SQL Operators
24.	PL SQL Programming
25.	Cursor
26.	Collections
27.	Function Procedure Packages
28.	Trigger
29.	SQL PLUS Session Environment
30.	System Tables Data Dictionary
31.	System Packages
32.	Object Oriented
33.	XML
34.	Large Objects
35.	Transaction
36.	User Privilege

Java

Java Tutorial

Java Source Code / Java Documentation

Illustrator Tutorials

GIMP Tutorials

C# / C Sharp

C# / CSharp Tutorial

C# / CSharp Open Source

SQL Server / T-SQL Tutorial

Flash / Flex / ActionScript

VBA / Excel / Access / Word

XML

XML Tutorial

Microsoft Office PowerPoint 2007 Tutorial

Microsoft Office Excel 2007 Tutorial

Microsoft Office Word 2007 Tutorial

Oracle PL/SQL Tutorial » Query Select » CASE

2. 15. 1. Using the CASE Expression

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>

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>

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>

SQL>

2. 15. CASE
	2. 15. 1.	Using the CASE Expression
	2. 15. 2.	The following example illustrates the use of a simple CASE expression:
	2. 15. 3.	Using Searched CASE Expressions
	2. 15. 4.	The following example illustrates the use of a searched CASE expression:
	2. 15. 5.	Use logical operators in a searched CASE expression
	2. 15. 6.	Use CASE statement to deal with NULL
	2. 15. 7.	Use case when and grouping function together
	2. 15. 8.	Use case when clause to decode value
	2. 15. 9.	Use case when statement with between ... and
	2. 15. 10.	Use case when statement with exists and subquery
	2. 15. 11.	Use case when statement with in()
	2. 15. 12.	Use case when statement with to_char() like
	2. 15. 13.	Use case when with comparasion operator
	2. 15. 14.	Use Case to output null value
	2. 15. 15.	Wrap case when into sum() function

www.java2java.com | Contact Us

All other trademarks are property of their respective owners.