WHERE CASE WHEN BETWEEN Two Months – ORA-00905: missing keyword

October 11, 2023

I’ve piggybacked this query (which works) from a different subquery, but I’ve added a CASE clause in the WHERE. It’s throwing me a ‘missing keyword’ error and I can’t see what the issue could be. Unfortunately I have no one around who can help me look for what I’m guessing is a very small error I’m not seeing.

The CASE essentially should be saying:

WHEN MONTH BETWEEN JAN AND JUN THEN START_DATE > 30/06/[CURRENT YEAR]
ELSE START_DATE > 30/06/[CURRENT YEAR] + 1

SELECT fp.object_id inv_id
      ,nvl(round(SUM((oc.finish_date - oc.start_date) * oc.slice)),0) forecast_amt
FROM odf_ssl_cst_dtl_cost oc
left join fin_cost_plan_details fd on fd.id  = oc.prj_object_id
left join fin_plans fp on fp.id = fd.plan_id
    and fp.plan_type_code = 'FORECAST'
    and fp.is_plan_of_record = 1 
WHERE CASE
    WHEN MONTH(GETDATE()) BETWEEN 1 AND 6 
        THEN oc.start_date > DATEADD(MONTH,6,DATEADD(DD,-1,DATEADD(YYYY, DATEDIFF(YYYY,0,GETDATE()),0)))
    ELSE oc.start_date > DATEADD(MONTH,6,DATEADD(DD,-1,DATEADD(YYYY, DATEDIFF(YYYY,0,GETDATE())+1,0)))
END
GROUP BY fp.object_id

>Solution :

CASE returns a value, but you can’t use it to return a boolean to a WHERE condition.

Rewrite the WHERE clause to move the comparison outside the CASE and have the CASE just return the value to be compared:

WHERE oc.start_date >
    CASE
         WHEN MONTH(GETDATE()) BETWEEN 1 AND 6 
            THEN DATEADD(MONTH,6,DATEADD(DD,-1,DATEADD(YYYY, DATEDIFF(YYYY,0,GETDATE()),0)))
        ELSE DATEADD(MONTH,6,DATEADD(DD,-1,DATEADD(YYYY, DATEDIFF(YYYY,0,GETDATE())+1,0)))
    END