Stack Exchange
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

Sample data and desired results would certainly help.  The question is pretty clear.  The code sample seems to have nothing to do with the question.


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.

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

