Mastering Oracle SQL

DECODE and CASE Examples

The following sections present a variety of examples illustrating the uses of conditional logic in SQL statements. While we recommend that you use the CASE expression rather than the DECODE function, where feasible we provide both DECODE and CASE versions of each example to help illustrate the differences between the two approaches.

Result Set Transformations

You may have run into a situation where you are performing aggregations over a finite set of values, such as days of the week or months of the year, but you want the result set to contain one row with N columns rather than N rows with two columns. Consider the following query, which aggregates sales data for each quarter of 2001:

               SELECT TO_CHAR(order_dt, 'Q') sales_quarter, 
                 SUM(sale_price) tot_sales
               FROM cust_order
               WHERE order_dt >= TO_DATE('01-JAN-2001','DD-MON-YYYY')
                 AND order_dt < TO_DATE('01-JAN-2002','DD-MON-YYYY')
               GROUP BY TO_CHAR(order_dt, 'Q')
               ORDER BY 1;

S  TOT_SALES
- ----------
1    9739328
2   10379833
3    9703114
4    9772633

In order to transform this result set into a single row with four columns, we need to fabricate a column for each quarter of the year and, within each column, sum only those records whose order date falls in the desired quarter. We can do that with DECODE:

               SELECT 
                 SUM(DECODE(TO_CHAR(order_dt, 'Q'), '1', sale_price, 0)) Q_1,
                 SUM(DECODE(TO_CHAR (order_dt, 'Q'), '2', sale_price, 0)) Q_2,
                 SUM(DECODE(TO_CHAR (order_dt, 'Q'), '3', sale_price, 0)) Q_3,
                SUM(DECODE(TO_CHAR (order_dt, ...