CODEWITHSUNDEEP
sqlcasewhere-clausebetween
Venkat
Venkat

Reputation: 1

How to add sql "BETWEEN" conditions inside CASE within WHERE CLAUSE in SQL

I have a sql query where I need to add different statements(between statements) in where clause based on one condition. If TX.APPLICATION_ID is 'CONV' then I need to add filter date range on SYS_CREATION_DATE and if APPLICATION_ID is not 'CONV' then I need to add filter date range on TRANS_DATE. Here is what i'm trying and getting 'missing keyword' error. please suggest

WHERE TX.TRANS_TYPE IN ('CREDIT', 'CREDITR','CREDITR9', 'DSPJUS')  
  AND 
    CASE
    WHEN TRIM(TX.APPLICATION_ID) = 'CONV' 
    THEN 
      TX.SYS_CREATION_DATE  BETWEEN  TO_DATE('20200831', 'YYYYMMDD')  AND  TO_DATE('20200831', 'YYYYMMDD')
    ELSE 
       TX.TRANS_DATE BETWEEN TO_DATE('20200831', 'YYYYMMDD') AND TO_DATE('20200831', 'YYYYMMDD')
    END

Upvotes: 0

Views: 420

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521864

You should remove the CASE expression logic, which generally never belongs in the WHERE clause. Instead, use vanilla AND/OR:

WHERE
    TX.TRANS_TYPE IN ('CREDIT', 'CREDITR','CREDITR9', 'DSPJUS') AND (
    (
        TRIM(TX.APPLICATION_ID) = 'CONV' AND
        TX.SYS_CREATION_DATE >= date '20200831' AND
        TX.SYS_CREATION_DATE < date '20200901'
    )
    OR
    (
        TRIM(TX.APPLICATION_ID) <> 'CONV' AND
        TX.TRANS_DATE >= date '20200831' AND
        TX.TRANS_DATE < date '20200901'
    ))

Upvotes: 1

Related Questions