Oracle case missing keyword

0
votes

i am not able to figure out what keyword is missing when i tried to execute below query, what i want is if startDate and endDate are present use between else return sysdate-7 data.

    select * from LARGE_RELATION LR where
     (CASE WHEN (STARTDATE IS NOT NULL AND ENDDATE IS NOT NULL)
           THEN LR.END_DT BETWEEN to_date(STARTDATE, 'yyyymmdd') and to_date(ENDDATE, 'yyyymmdd')
           ELSE (LR.END_DT IS NULL OR LR.END_DT > SYSDATE - 7)
           END);

ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action: Error at Line: 3 Column: 31

2
sqloracledatecasebetween
CASE is an statement and return a value not a condition – Juan Carlos Oropeza
What you posted is a PL/SQL code block. You can't use SQL SELECT statements in PL/SQL. What you can use is SELECT ... INTO ... but you make no attempt to use that - you didn't declare a variable to accept the value returned by your SELECT. It is really not clear what you are trying to do, but the error has to do with this invalid use of SELECT within PL/SQL code. By the way, the error message points to the beginning of the SELECT statement, not the CASE thingy (which is on a different line of code). – mathguy

2 Answers

1
votes

First condition handle when both date are not null

Second one validate when one of the dates is null

select * 
from LARGE_RELATION LR 
where
    (    STARTDATE IS NOT NULL 
     AND ENDDATE IS NOT NULL
     AND LR.END_DT BETWEEN to_date(STARTDATE, 'yyyymmdd') 
                       AND to_date(ENDDATE, 'yyyymmdd')
    ) OR
    (     (STARTDATE IS NULL OR ENDDATE IS NULL)
      AND (LR.END_DT IS NULL OR LR.END_DT > SYSDATE - 7)
    )
0
votes

you need to specify the parameter you want to check after case in your example - CASE STARTDATE WHEN IS NOT NULL AND (ENDDATE IS NOT NULL)