ORA-00905: missing keyword in case statement

Tags: , , ,



I am getting ORA-00905: missing keyword in case statement in my when clause. Below is the query.

  vsqlstr := 'select name, enrollement_dt,case_name, dept, subject, city, state, zip from enrollement where ';
    vsqlstr :=vsqlstr ||' 
    AND CASE
        WHEN TO_CHAR(SYSDATE,''MM'') <= ''06'' THEN enrollement_dt <= to_date(''12''||(EXTRACT(YEAR FROM SYSDATE)-1), ''MMYYYY'') 
        ELSE enrollement_dt >= to_date(''07''||(EXTRACT(YEAR FROM SYSDATE)), ''MMYYYY'') 
    END ';

Answer

You can’t have a boolean value as a selectable something in a query in oracle, you can only make boolean expressions in e.g. the WHERE/ON clauses etc

i.e. this is invalid:

select case when 1=1 then 2>3 else 4>5 end from dual
                          ^^^
    can't have something that evaluates to a boolean type here

This is valid:

select case when 1=1 then 'TRUE' else 'FALSE' end from dual

You could later compare these values to something to realize a boolean:

WHERE CASE WHEN x=y THEN 'T' ELSE 'F' END = 'T'

But you can’t use booleans on their own.. This is also invalid:

WHERE CASE WHEN x=y THEN 1=1 ELSE 1=0 END

in your case, promote the booleans the case is trying to realize, into the WHERE predicates:

WHERE (
 /*CASE 
     WHEN*/ TO_CHAR(SYSDATE,''MM'') <= ''06'' /*THEN*/ AND enrollement_dt <= to_date(''12''||(EXTRACT(YEAR FROM SYSDATE)-1), ''MMYYYY'')
  ) OR 
    /*ELSE*/ enrollement_dt >= to_date(''07''||(EXTRACT(YEAR FROM SYSDATE)), ''MMYYYY'') 
/*END*/

(I left the case when in as comments to show you what was edited)



Source: stackoverflow