Re: SELECT COUNT(CASE WHEN.... question

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Dec 2003 18:21:53 GMT
Message-ID: <bqqic0$24i8vh$1@ID-82536.news.uni-berlin.de>

> I cannot find documentation of this behaviour in the Oracle docs and
> would appreciate a reference (if there is one). For some reason it
> appears that the SELECT statement in a cursor cannot use the "CASE
> WHEN" construct.
> The first SQL query = okay.
> The second SQL query, as a cursor, is syntactically incorrect.
> Thank you
> Barry
>
>
> SQL> CREATE TABLE tblTest(a NUMBER,b NUMBER);
>
> Table created.
>
> SQL>
> SQL> SELECT a
> 2 FROM tblTest
> 3 GROUP BY a
> 4 HAVING 16=COUNT(CASE WHEN b>46 THEN 1 ELSE NULL END);
>
> no rows selected
>
> SQL>
> SQL>
> SQL> DECLARE
> 2 CURSOR c1 IS
> 3 SELECT a
> 4 FROM tblTest
> 5 GROUP BY a
> 6 HAVING 16=COUNT(CASE WHEN b>46 THEN 1 ELSE NULL END);
> 7 BEGIN
> 8 NULL;
> 9 END;
> 10 /
> HAVING 16=COUNT(CASE WHEN b>46 THEN 1 ELSE NULL END);
> *
> ERROR at line 6:
> ORA-06550: line 6, column 23:
> 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>

>
>
> SQL>
> SQL> SELECT *
> 2 FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> PL/SQL Release 8.1.7.0.0 - Production
> CORE 8.1.7.0.0 Production
> TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production

As I can see, you are using 8i where the 'PL/SQL Engine's' SQL capabilities lag behind the 'SQL Engine', that is, some features that are new with 8i's SQL are not yet implemented in the PL/SQL Engine, most notable the analytical functions and, as in your case, the case statement.

If you cannot upgrade to 9i, you have to resort to execute immediate (where this is no problem) or to this things the 'old fashioned' way.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Fri Dec 05 2003 - 12:21:53 CST