Database Reference
In-Depth Information
TO_DATE function to choose only those rows that fall in the year 2002
using a WHERE clause.
WHERE TO_CHAR(SESSION_DATE,'YYYY') = '2002'
To sift a range of values using DECODE, you need a finite list of num-
bers. The AMOUNT_CHARGED is converted into the next highest 100,
providing values such as 100, 500, 1200, and so on.
TRUNC(AMOUNT_CHARGED,-2)+100
The following list can be used in the DECODE function. We want the
DECODE function to sort values into the following categories:
If the AMOUNT_CHARGED is less than $400, this is a “Low Risk”
amount.
If the AMOUNT_CHARGED is at least $400 but less than $700,
this is a “Medium Risk” amount.
Otherwise, this is a “High Risk” amount.
Here is the complete query, including the DECODE function. The
results are sorted by the AMOUNT_CHARGED and shown in Figure
9.16.
SELECT AMOUNT_CHARGED
,TRUNC(AMOUNT_CHARGED,-2)+100 NEXT_HIGHEST_HUNDRED
, DECODE (ROUND(AMOUNT_CHARGED,-2)+100,
100, 'Low Risk',
200, 'Low Risk',
300, 'Low Risk',
400, 'Low Risk',
500, 'Med Risk',
600, 'Med Risk',
700, 'Med Risk',
'High Risk') RISK_FACTOR
FROM STUDIOTIME
WHERE TO_CHAR(SESSION_DATE,'YYYY') = '2002'
ORDER BY 1;
