I'm using Oracle SQL Developer. Really new to SQL and not sure why I'm getting this error.

The error:

ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action: Error at Line: 1 Column: 128

SELECT r.ROOM_ID, w.HEAD_NURSE, w.WARD_NAME, p.LEAVING_DATE, count(CASE p.PATIENT_ID when to_date('2022-01-07', 'YYYY-MM-DD') < p.LEAVING_DATE then 1 else null END) AS No_Of_Patients_In_Room
FROM ROOM r, WARD w, PATIENT_DETAILS p
WHERE r.HEAD_NURSE = 'Some Nurses Name' AND w.WARD_NAME = 'Some Ward Name' AND p.ROOM_ID = r.ROOM_ID
group by r.ROOM_ID, w.HEAD_NURSE, w.WARD_NAME, p.LEAVING_DATE;

It should only count the PATIENT_ID when the LEAVING_DATE is after the date specified. I could just remove the data that has a LEAVING_DATE that has already gone past the date but I would still like to know what is causing this error.

CodePudding user response：

The CASE syntax has 2 forms.

Don't mix them.

select n
, case n
  when 1 then 'A'
  when 2 then 'B'
  else 'C'
  end as form1
from (select 1 as n from dual) q

 N | FORM1
-: | :----
 1 | A    

select n
, case 
  when n=1 then 'A'
  when n>1 then 'B'
  else 'C'
  end as form2
from (select 2 as n from dual) q

 N | FORM2
-: | :----
 2 | B    

select n
, case n
  when n=1 then 'A'
  when n>1 then 'B'
  else 'C'
  end as badmix
from (select 3 as n from dual) q

ORA-00905: missing keyword

db<>fiddle here

So this is wrong:

CASE p.PATIENT_ID when to_date('2022-01-07', 'YYYY-MM-DD') < p.LEAVING_DATE then 1 else null END