Oracle Case in WHERE Clause with multiple conditions

user_16545420 0
Viewed 353

I'm trying to pass a 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 else 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

case select oracle sql
2 Answers

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(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 < 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
    (to_char(sysdate,'Day') != 'Sunday'  AND
     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))
user_24227760

The CASE statement returns a "column value" that cannot be evaluated as a WHERE CONDITION itsef, but you can use it as a value 1 or 3 depending on sysdate, and then use this value in the filter condition:

WHERE
TRUNC(C.DISBURSALDATE) >=
TRUNC(
    SYSDATE  - 
    (CASE WHEN to_char(sysdate,'Day') = 'Sunday' THEN 3 ELSE 1 END) -- this case will return 3 or 1, and use it to subtract from sysdate
    )
AND TRUNC(C.DISBURSALDATE) < TRUNC(SYSDATE) --this is just because I am not sure if your data has dates after today

Also, you do not need the clauses below to filter the dates:

to_char(C.DISBURSALDATE , 'mm') = to_char(sysdate-1, 'mm') AND
to_char(C.DISBURSALDATE , 'yyyy') = to_char(sysdate-1, 'yyyy')
user_164948860
Related