Re: Using CASE WHEN expression in Package Procedure is not allowed?

Roel Schreurs wrote:
> I try to use a CASE WHEN expression in the select list of a query. The
> query result should be returned by a ref cursor. I get a compile time
> error.
> PLS-00103: Encountered the symbol "CASE" when expecting one of the
> following:
> ( - + mod null <an identifier>
> <a double-quoted delimited-identifier> <a bind variable>
> table avg count current
> Why is this not allowed? I can use other types of expressions, like
> DECODE. Also, I can executed the query as a plain SQL statement. I am
> just curious to know the cause, I have implemented a work-around, so
> no help is needed there.
> My database version 8.1.7.
> Below an example that will show the compile error.
>
> CREATE OR REPLACE PACKAGE Test
> AS
> TYPE MyCursorType IS REF CURSOR;
> PROCEDURE TestCase(MyCursor OUT MyCursorType);
> END;
> /
> CREATE OR REPLACE PACKAGE BODY Test
> AS
> PROCEDURE TestCase(MyCursor OUT MyCursorType)
> IS
> BEGIN
> OPEN MyCursor FOR
> SELECT SYSDATE
> , CASE WHEN MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'ss')), 2) = 0 THEN
> 'Even' ELSE 'Odd' END AS EvenOdd
> FROM DUAL;
> END;
> END;
> /

Roel,

iirc, case when syntax is not allowed in 8i; it was introduced in 9iR2...

-- 

Regards,
Frank van Bortel