Oracle case when usage

The CASE expression can implement the if-then-else logic in SQL without using PL/SQL. The CASE method is similar to DECODE (), but CASE should be used because it is ANSI compatible.
Note:

1. Start with CASE and END with END

2. WHEN followed by a condition in the branch, THEN indicates the display result.

3. ELSE is the default value. It is similar to the default value of switch case in a high-level language program.

4. END followed by alias

CASE has two expressions:

1. Use a simple CASE expression to determine the return value.

Syntax:

CASE search_expression

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

ELSE default_result

END

Example:

Select product_id, product_type_id,

Case product_type_id

When 1 then 'book'

When 2 then 'video'

When 3 then 'dvd'

When 4 then 'cd'

Else 'magazine'

End

From products

Result:

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD

---------------------------------

1 Book

2 1 Book

3 2 Video

4 2 Video

5 2 Video

6 2 Video

7 3 DVDs

8 3 DVDs

9 4 CD

10 4 CD

11 4 CD

12 Magazine

12 rows selected.

2. Search for the CASE expression and use the condition to determine the return value.

Syntax:

CASE

WHEN condition1 THEN result1

WHEN condistion2 THEN result2

...

WHEN condistionN THEN resultN

ELSE default_result

END

Example:

Select product_id, product_type_id,

Case

When product_type_id = 1 then 'book'

When product_type_id = 2 then 'video'

When product_type_id = 3 then 'dvd'

When product_type_id = 4 then 'cd'

Else 'magazine'

End

From products

The result is the same as above.