APEX

Hi,

I'm trying to get this CASE statement to work in Apex 23.1.2 PL/SQL. It is part of the LOV for the [contractor_id] field. I cannot compile it as it is throwing the ‘Missing Keyword’ error.

SELECT first_name  || ' ' || last_name, id,
CASE WHEN :P140_NEXT_WEEK != 1  THEN 
id NOT IN (SELECT contractor_id FROM eba_demo_cal_sessions
    WHERE  to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') = start_date AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') = end_date
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') < start_date AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') > start_date
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') > start_Date AND to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') < end_date
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') = start_date AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') < end_date)
    order by first_name
ELSE
   
id NOT IN (SELECT contractor_id FROM eba_demo_cal_sessions
    WHERE to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7  = start_date  AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 = end_date 
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 < start_date  AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 > start_date 
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 > start_Date  AND to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 < end_date 
    OR to_date(:P140_START_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 = start_date  AND to_date(:P140_END_DATE,'fmDay MM/DD/YYYY HH:MIPM') + 7 < end_date )
    order by first_name 
END
FROM contractors

Both individual queries work on their own but can't figure out how to get this CASE with SUBQUERY query to work.

Any help is greatly appreciated.

doug