CODEWITHSUNDEEP
sqloracle-databaseif-statementcasebetween
Santosh
Santosh

Reputation: 1

CASE SQL statement oracle database

my requirement is if first condition is null then check for the second condition if second condition is null as well then third condition in oracle database, so I am trying to achieve this by case statement as shown below but getting an error as missing expression/missing keyword.

CASE
     WHEN (TRUNC( OTA_EVENTS.COURSE_START_DATE) BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE ) IS NULL  
     then  (TRUNC(OTA_DELEGATE_BOOKINGS.Date_Booking_Placed) BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE) 
     ELSE (TRUNC(OTA_DELEGATE_BOOKINGS.DATE_STATUS_CHANGED) BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE) 
END

Please help if there is any other way to achieve this

Upvotes: 0

Views: 58

Answers (2)

Popeye
Popeye

Reputation: 35900

It means you need OR between your conditions:

(TRUNC(OTA_EVENTS.COURSE_START_DATE) 
            BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE 
                AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
OR ( TRUNC(OTA_DELEGATE_BOOKINGS.DATE_BOOKING_PLACED) 
            BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE 
                AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE )
OR ( TRUNC(OTA_DELEGATE_BOOKINGS.DATE_STATUS_CHANGED) 
            BETWEEN PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE 
                AND PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE ))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

my requirement is if first condition is null then check for the second condition if second condition is null as well then third condition in oracle database

I don't see what your code has to do with this question (it is using between rather than NULL comparison. ABut what you are describing is COALESCE():

select coalesce(a, b, c)

This can take as many arguments as you need. It returns the first non-NULL value.

Upvotes: 1

Related Questions