“Between” CASE and DECODE

Here is a select statement that demonstrates the following:

1. Use of CASE to do a “between” logic.
2. Use of DECODE with SIGN to do a “between” logic.
3. How easy and clear to use CASE as compared to using DECODE.

CREATE TABLE t 
    (activity_date date, date_from date, date_to DATE)
/
INSERT INTO t 
    VALUES (SYSDATE, SYSDATE - 1, SYSDATE + 1)
/
INSERT INTO t 
    VALUES (SYSDATE, SYSDATE - 10, SYSDATE - 5)
/
INSERT INTO t 
    VALUES (SYSDATE, SYSDATE + 1, SYSDATE + 5)
/
SELECT 
   activity_date, 
   date_from, 
   date_to,
   CASE WHEN activity_date 
                   BETWEEN date_from AND date_to 
            THEN 'between' 
            ELSE 'not between' END 
   AS case_result,
   DECODE(SIGN(activity_date - date_from), -1, 
           'not between', 
         DECODE(SIGN(date_to - activity_date), -1, 
             'not between', 
           'between' ) ) 
   AS decode_result
FROM t
/
DROP TABLE t
/

Related articles:

• CASE gotcha in Oracle 8i
• Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
• The Lazy Developer’s Way to Populate a Surrogate Key

Filed in Oracle on 22 Jun 05 | Tags: case, decode, function, sign

---