Oracle-L: Re: CASE under Oracle8i

From: <Peter.McLarty_at_mincom.com>
Date: Sun, 24 Mar 2002 20:38:18 -0800
Message-ID: <F001.00431B20.20020324203818@fatcity.com>

SQL> SELECT CASE
  2 WHEN DUMMY='X' THEN 'Dual is OK'
  3 ELSE 'Dual is messed up'
  4 END
  5 FROM DUAL; CASEWHENDUMMY='X'THEN'DUALISOK

Dual is OK

DECLARE
2 dual_message VARCHAR2(20);
3 BEGIN

  4     SELECT CASE
  5     WHEN DUMMY='X' THEN 'Dual is OK'
  6     ELSE 'Dual is messed up'
  7     END INTO dual_message
  8     FROM DUAL;
  9     DBMS_OUTPUT.PUT_LINE(dual_message);

10 END;
11 /

DECLARE
   dual_message VARCHAR2(20);
BEGIN
   SELECT CASE
   WHEN DUMMY='X' THEN 'Dual is OK'
   ELSE 'Dual is messed up'
   END INTO dual_message
   FROM DUAL;
   DBMS_OUTPUT.PUT_LINE(dual_message);
END;

ORA-06550: line 4, column 11:
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> table avg count current distinct max min prior sql stddev sum unique variance execute the forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>

SQL> Yep that about sums it up, the second one doesn't work in 8.1.7.0.0

Cheers

Peter McLarty               E-mail: Peter.Mclarty_at_mincom.com
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                            Facsimile: +61 (0)7 3303 3048
=================================================

A great pleasure in life is doing what people say you cannot do.

Walter Bagehot (1826-1877 British Economist)
Mincom "The People, The Experience, The Vision"

Jonathan Gennick <listmail_at_gennick.com>
Sent by: root_at_fatcity.com
25/03/2002 01:18 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Fax to: 
        Subject:        CASE under Oracle8i

If you run Oracle8i, and could conveniently test a couple of statements for me, I'd appreciate it.

First, I believe the following should work under Oracle8i:

SELECT CASE
WHEN DUMMY='X' THEN 'Dual is OK'
ELSE 'Dual is messed up'
END
FROM DUAL; I'm less certain about the following, which I vagualy recall hearing might not work under Oracle8i, but which does work under Oracle9i:

The point of all this is that I seem to recall hearing that, while SQL in 8i supported the CASE statement, that SQL within PL/SQL did not. I'm trying to verify the truth or falsity if that statement.

Jonathan Gennick --- Brighten the corner where you are mailto:jonathan_at_gennick.com
http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonathan Gennick
INET: listmail_at_gennick.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Peter.McLarty_at_mincom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message