Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: CASE within COUNT() working in sqlplus but not in package body!!
In article <95792d9e.0201212342.28514149_at_posting.google.com>,
john_at_softgame.com.au says...
>
>Using Oracle 8.1.7.2.0 on Solaris 8.
>
see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:186812348071
the problem is the same and the solutions offered apply
>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?
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Tue Jan 22 2002 - 06:34:40 CST