DECODE only does exact matching. For example, exact values representing
"Today" and "Tomorrow" are easy to get, so you might use DECODE like this:
SELECT due_dt,
DECODE ( TRUNC (due_dt),
TRUNC (SYSDATE), 'Today',
TRUNC (SYSDATE + 1), 'Tomorrow',
'Neither'
) AS due_text
FROM tablex;
When you want to compare values (e.g., "Is date a before
date b?," or "Is date a between dates b1 and b2?")
Then the CASE statement is much more convenient.
SELECT due_dt,
CASE
WHEN TRUNC (due_dt) < TRUNC (SYSDATE)
THEN 'Past'
WHEN TRUNC (due_dt) BETWEEN TRUNC (SYSDATE)
AND TRUNC (SYSDATE) + 6
-- Note: BETWEEN includes both end-points, so
-- SYSDATE+0 through SYSDATE+6 is a full week
THEN 'This Week'
ELSE 'Later'
END AS due_text
FROM tablex;
CASE is a feature of ANSI SQL introduced into Oracle in version 8.1.
See the earlier question on
DECODE versus CASE for more examples.
|
