Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CASE within COUNT() working in sqlplus but not in package body!!

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 22 Jan 2002 04:34:40 -0800
Message-ID: <a2jm900dno@drn.newsguy.com>


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 Corp 
Received on Tue Jan 22 2002 - 06:34:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US