Common expressions in Oracle case 2

Oracle case usage. We all know that Oracle CASE expressions can be used in SQL to implement the if-then-else logic. PL/SQL is not required. In fact, CASE works in a similar way as DECODE (), but CASE should be used because it is compatible with ANSI.

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 Oracle CASEPROD  
  
  
---------- --------------- --------  
  
  
1 Book  
  
  
1 Book  
  
  
2 Video  
  
  
2 Video  
  
  
2 Video  
  
  
2 Video  
  
  
3 DVD  
  
  
3 DVD  
  
  
4 CD  
  
  
4 CD  
  
  
4 CD  
  
  
Magazine  
  
  
rows selected.  
 
 

2. Search for the Oracle 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.