What is the CASE statement?The CASE statement is an alternative implementation in Structured Query Language (SQL) of the 'if..then..else' logic that is common to all programming languages. The CASE statement has a range of applicability across a wide range of databases including versions of ORACLE later than 8i (8.1.5).
Consider the following example for the use of the CASE statement. Let the table FACT_CUST be constructed as follows:
cust_no | cust_name | cust_order | cust_type |
A01 | John Doe | 2 | 2 |
A02 | Jane Doe | 3 | 2 |
A02 | Jane Doe | 7 | 2 |
A03 | Joe Doe | 6 | 3 |
| Joe Doe | 3 | 3 |
A04 | Jenny Doe | 5 | 4 |
The SQL query below:
Select cust_name, (CASE WHEN cust_type <=3 THEN sum(cust_order)ELSE 0 END) my_values
From FACT_CUST
Where cust_no IS NOT NULL
Group by cust_name, cust_type
causes the following result set to be retrieved from FACT_CUST
cust_name | my_values |
Jane Doe | 10 |
Jenny Doe | 0 |
Joe Doe | 6 |
John Doe | 2 |
As apparent from the SQL statement, CASE statement in the above example causes the orders with 'cust_type <=3' to be aggregated while the orders of 'cust_type > 3' to be represented by 0. The CASE statement can be used to cover all various types of logical operands and its general format is given by:
CASE
WHEN <statement1> and/or <statement2> THEN <directive1>
WHEN <statement3> and/or <statement4> THEN <directive2>
ELSE <directive_default>
END
What is the DECODE statement?The DECODE function is another implementation of the 'if..then..else' logic in SQL by the ORACLE database. The format of the DECODE function is as follows:
DECODE(<value>, <statement1>, <directive1>, <directive_default>)
To understand the meaning of and the construction of DECODE, look at how the previous query would have been written using DECODE:
Select cust_name, (DECODE(SIGN(3-cust_type), -1, 0, sum(cust_order))) my_values
From FACT_CUST
Where cust_no IS NOT NULL
Group by cust_name, cust_type
Note that in this representation, DECODE is combined with the another Oracle function, SIGN( ), to check for the sign of the value '3-cust_type'; thus, if 'SIGN(3-cust_type)=-1' or equivalently 'cust_type > 4', then the DECODE function returns '0', else it returns 'sum(cust_order)', which is the qualification in the original CASE statement.
The following table gives the alternative DECODE statements for some of the popular conditional primitives:
Conditional primitive | DECODE statement |
A<B | Decode(Sign(A-B), -1, 1, 0) |
A<=B | Decode(Sign(A-B), 1, 0, 1) |
A>B | Decode( Sign(A-B), 1, 1, 0 ) |
A>=B | Decode( Sign(A- B), -1, 0, 1 ) |
A=B | Decode( A, B, 1, 0 ) |
A between B and C | Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 )) |
A is null | Decode(A,null,1,0) |
A is not null | Decode(A,null,0,1) |
A in (B1,B2,...,Bn) | Decode(A,B1,1,B2,1,...,Bn,1,0) |
