Re: Another angle on this....

From: Cameron Hutchison <camh_at_zip.com.au>
Date: Thu, 21 Feb 2002 13:24:13 +1100
Message-ID: <pan.2002.02.21.13.24.12.980680.5385@zip.com.au>

On Thu, 21 Feb 2002 11:53:15 +1100, damorgan wrote:

> I just ran the the following on an 8.1.7.0.0 Oracle instance:
>
> CREATE TABLE sources (
> sourceno NUMBER(10));
>
> INSERT INTO sources VALUES (500);
> INSERT INTO sources VALUES (510);
> INSERT INTO sources VALUES (511);
> INSERT INTO sources VALUES (525);
> INSERT INTO sources VALUES (526);
> COMMIT;
>
> CREATE OR REPLACE VIEW src_view AS
> SELECT sourceno, CASE WHEN (sourceno < 510) THEN 'low'
> WHEN (sourceno between 510 and 525) THEN 'med'
> ELSE 'high'
> END note
> FROM sources;
>
> SELECT *
> FROM src_view;
>
> If you can't get it to work ... then there is something else going on
> other than Oracle's software.

I'm no oracle expert, but I read a previous comment saying that CASE worked in SQL, not PL/SQL. Your example is SQL. I just tried:

BEGIN
SELECT sourceno, CASE WHEN (sourceno < 510) THEN 'low'

                      WHEN (sourceno between 510 and 525) THEN 'med'
                      ELSE 'high'
                 END note

END; on 8.1.7.0.0 and got an error:
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following: [truncated]

If you execute that same select statement without the BEGIN/END, it works.

I'd say it's Oracle software, not "something else going on". Received on Wed Feb 20 2002 - 20:24:13 CST