CODEWITHSUNDEEP
javasqloracle-databaseoracle11g
Geek
Geek

Reputation: 3329

ORA-00905: missing keyword in case statement

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 ';

Upvotes: 0

Views: 354

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is too long for a comment.

You are using a case EXPRESSION, not a case STATEMENT. This is not just a difference of worse. An expression in SQL is (usually) a scalar value of a particular type, such as number or string or date.

Oracle does not have a boolean data type. Hence, you cannot assign the result of a boolean expression to a variable. Nor can you return it from a case expression. But your logic is attempting to do that.

In general, putting case expressions in SQL is discouraged, because they impede the optimizer. In your case, you don't need one. Your outer logic should assign the approach comparison logic outside the SQL string. Then the one condition that is true should go into the SQL expression. The only reason not to do this would be if you were storing the SQL string somewhere, so it was going to be run at different times.

Upvotes: 0

Related Questions