Oracle SQL CASE Expressions and COALESCE Function

CASE EXPRESSIONS

A searched CASE expression evaluates a number of conditions and returns a result determined by which condition is true. The syntax for the SEARCHED CASE expression is as follows:

CASE 

WHEN C1 THEN R1 

WHEN C2 THEN R2

 ... 

WHEN CN THEN RN  ELSE RD

END

In the syntax definition, the “C”s represent conditions, and the “R”s represent results.

For Example:

SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,  CASE p.status   

WHEN 'INSTOCK' THEN 'In Stock'    WHEN 'DISC' THEN 'Discontinued'   
WHEN 'BACKORD' THEN 'Backordered'   

WHEN 'ENROUTE' THEN 'Arriving Shortly'   

WHEN 'UNAVAIL' THEN 'No Shipment Scheduled'   

ELSE 'Unknown' 

END part_status

FROM part p, supplier s

WHERE p.supplier_id = s.supplier_id;

COALESCE

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null. The coalesce function will compare each value, one by one.

Syntax

COALESCE (Expr1,Expr2,…Exprn)

Example

Select COALESCE( address1, address2,address3) result

FROM Suppliers;