|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
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.
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
