CASE within COUNT() working in sqlplus but not in package body!!

Using Oracle 8.1.7.2.0 on Solaris 8.

There seems to be some problem using a CASE expression within COUNT(). Now Oracle allows this ( The syntax for COUNT() allows for an expression, and CASE is an expression ).

Just for the purpose of illustration, consider the following:

SQL> SELECT sysdate, COUNT( CASE WHEN sysdate = sysdate THEN NULL ELSE 1 END )
  FROM dual;

SYSDATE COUNT(CASEWHENSYSDATE=SYSDATETHENNULLELSE1END)

--------- ----------------------------------------------
22-JAN-02                                              0

The above does not really make any sense, but the it is just an illustration really. At least you can see it works fine in sqlplus.

Now put that in a package / package body:

CREATE OR REPLACE PACKAGE "AGGREGATOR"."SAMPLE" AS PROCEDURE test;
END sample;
/

CREATE OR REPLACE PACKAGE BODY "AGGREGATOR"."SAMPLE"     CREATE OR REPLACE PACKAGE BODY sample AS PROCEDURE test
IS
 sampleDate DATE;
 myCount NUMBER;
BEGIN
  SELECT sysdate, COUNT( CASE WHEN sysdate = sysdate THEN NULL ELSE 1 END )
  INTO sampleDate, myCount
  FROM dual;
END test;
END sample;
/

... and the package body is marked as INVALID, Looking at USER_ERRORS, you get:

PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

   ( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
   count current distinct max min prior sql stddev sum unique    variance execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

What is going on??? Why does it work in sqlplus and not in a package body?
Any ideas? Received on Tue Jan 22 2002 - 01:42:58 CST