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

From: Frank <fbortel_at_nescape.net>
Date: Sat, 06 Dec 2003 16:18:35 +0100
Message-ID: <bqsrg0$ehr$1@news4.tilbu1.nb.home.nl>

Rene Nyffenegger wrote:

>>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
> 
> 

The proof:

   1 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;
SQL> / PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

-- 
Regards, Frank van Bortel

Received on Sat Dec 06 2003 - 09:18:35 CST