Replacing DECODEs with IF statements

The DECODE function offers IF-like capabilities in the nonprocedural SQL environment provided by the Oracle Server. You can use the DECODE syntax to create matrix reports with a fixed number of columns or to perform complex IF-THEN-ELSE logic within a query. The downside to DECODE is that it can be difficult to write and very difficult to maintain. Consider the following example of using DECODE to determine whether a date is within the prescribed range and, if so, to add to the count of rows that fulfill this requirement:

SELECT FC.year_number, SUM (DECODE (GREATEST (ship_date, FC.q1_sdate), ship_date, DECODE (LEAST (ship_date, FC.q1_edate), ship_date, 1, 0), 0)) Q1_results, SUM (DECODE (GREATEST (ship_date, FC.q2_sdate), ship_date, DECODE (LEAST (ship_date, FC.q2_edate), ship_date, 1, 0), 0)) Q2_results, SUM (DECODE (GREATEST (ship_date, FC.q3_sdate), ship_date, DECODE (LEAST (ship_date, FC.q3_edate), ship_date, 1, 0), 0)) Q3_results, SUM (DECODE (GREATEST (ship_date, FC.q4_sdate), ship_date, DECODE (LEAST (ship_date, FC.q4_edate), ship_date, 1, 0), 0)) Q4_results FROM orders O, fiscal_calendar FC GROUP BY year_number;

The result set for this query might look like this:

YEAR NUMBER Q1 RESULTS Q2 RESULTS Q3 RESULTS Q4 RESULTS------------ ---------- ---------- ---------- ----------1993 12000 14005 22000 400001994 10000 15000 21000 55004

While it is very handy to use DECODE to produce such a report, the SQL required to accomplish the task is more than a little frightening. Here is how you might try to interpret the Q1 RESULTS nested DECODE:

If the ship date is greater than or equal to the first quarter start date and less than or equal to the first quarter end date, then add one to the sum of the total number of orders shipped in that quarter. Otherwise, add zero.

Unfortunately, unless you are experienced in interpreting DECODE statements, you may find it difficult to glean this understanding from that convoluted SQL statement. The repetition in that single SELECT also cries out for modularization, which we can supply with the following stored function (incr_in_range means "increment if in the range"):

FUNCTION incr_in_range (ship_date_in IN DATE, sdate_in IN DATE, edate_in IN DATE) RETURN INTEGERISBEGIN IF ship_date_in BETWEEN sdate_in AND edate_in THEN RETURN 1; ELSE RETURN 0; END IF;END;

Yep, that's all there is to it! With the incr_in_range function, that long and winding SELECT statement simply becomes:

SELECT FC.year_number, SUM (incr_in_range (ship_date, q1_sdate, q1_edate)) Q1_results, SUM (incr_in_range (ship_date, q2_sdate, q2_edate)) Q2_results, SUM (incr_in_range (ship_date, q3_sdate, q3_edate)) Q3_results, SUM (incr_in_range (ship_date, q4_sdate, q4_edate)) Q4_results FROM orders O, fiscal_calendar FC GROUP BY year_number;

This stored function gets rid of the code redundancy and makes the SELECT statement much more readable. In addition, this function could be used in other SQL statements to perform the same logic.