CODEWITHSUNDEEP
sqloracle-databasedatecasebetween
2787184
2787184

Reputation: 3881

Oracle case missing keyword

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

Upvotes: 0

Views: 328

Answers (2)

DrunkTolstoy
DrunkTolstoy

Reputation: 36

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)

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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)
    )

Upvotes: 1

Related Questions