Oracle Case in WHERE Clause with multiple conditions

Question

i'm trying to pass case statement in my where clause but it seems i'm missing something or it's not possible in my scenario below is my code

explanation of the code "if the sysdate is Sunday i want to retrieve data for the past 3 days esle take yesterday data "

    FROM lea_nf1.lea_agreement_dtl c--, lea_nf1.LEA_GUARANTOR_HIRER_DTL d
   WHERE
            case when to_char(sysdate,'Day') = 'Sunday' then 
            to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm')
            and to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')
            and C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)
            else 
            to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm')
            and (to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')
            and C.DISBURSALDATE < sysdate end
ORDER BY C.DISBURSALDATE desc

below is the error

ORA-00905: missing keyword

appreciate your support.

Mureinik Mureinik · Accepted Answer · 2021-09-10T18:25:34

As you've seen, you can't use a case statement to build a where clause like this. What you could do, however, is use logical operators to get the desired behavior:

WHERE
    (to_char(sysdate,'Day') = 'Sunday' AND 
     to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
     to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
     C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)) OR
    (to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
     to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
     C.DISBURSALDATE < sysdate)

This can of course be simplified by extracting the common conditions on both sides of the or operator:

WHERE
    to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
    to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
    (to_char(sysdate,'Day') = 'Sunday' AND 
     C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1)) OR
    (C.DISBURSALDATE < sysdate)

Or, if you take it a step further, any C.DISBURSALDATE that is between trunc(sysdate-3) and trunc(sysdate-1) is also always < sysdate, so:

WHERE
    to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
    to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy') AND
    C.DISBURSALDATE < sysdate AND
    (to_char(sysdate,'Day') != 'Sunday' OR
     C.DISBURSALDATE between trunc(sysdate-3) and trunc(sysdate-1))

Oracle Case in WHERE Clause with multiple conditions

1 Answers