SELECT COUNT(CASE WHEN.... question

From: barry <bbulsara23_at_hotmail.com>
Date: 5 Dec 2003 09:14:25 -0800
Message-ID: <747f1dec.0312050914.395fb311@posting.google.com>

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

SQL>
SQL> Received on Fri Dec 05 2003 - 11:14:25 CST