Pseudo-IF in SQL SELECT

I had an app that needed to produce a grouping value of 5, 10, 15, or 20, based on date ranges. For the following date ranges, I needed that field to be as shown:

5 for 7/1/88-6/30/93
10 for 7/1/83-6/30/88
15 for 7/1/78-6/30/83
20 for earlier dates

Since Oracle's SQL doesn't have the equivalent of FoxPro's IIF() function, I had to simulate an IF function in SQL using the DECODE and SIGN functions.   (I used the SQL's WHERE clause to filter out records later than 6/30/93.)

The DECODE function returns a value based on an equality check (as in decode( test_variable, value1, return1, value2, return2, ..., else_return)), and doesn't allow other comparisons, such as ranges, greater than, less than, etc.  The SIGN function returns 1 if its parameter is positive, 0 if zero, or -1 if negative. So, I subtracted the last date in the prior date range from the record's date, which gives a positive number of days if the record's date is in the tested date range, or zero or less days if the record's date comes before the tested date range. This gives a value I can test with the SIGN function. If the number of days is positive, the SIGN function returns 1, and the DECODE test for 1 succeeds, returning the grouping value for that range. Otherwise, subsequent DECODE functions are embedded for the prior date range checks similar to the first one, except for the fourth date range for which the third date range's "else" value is used to return group 20.  The resulting snippet from the SQL statement is shown below:

decode(sign(trunc(hire_date)-to_date('30-JUN-88')),1,5,
decode(sign(trunc(hire_date)-to_date('30-JUN-83')),1,10,
decode(sign(trunc(hire_date)-to_date('30-JUN-78')),1,15,20)))

So, if you want to simulate an if..then..elseif..else.. type of structure in your SQL, the DECODE and SIGN functions, or other functions you may use with the DECODE function, can be used to do the job.

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.