Re: Another angle on this....

As soon as you typed BEGIN and END you broke it. It will work in a PL/SQL block in 9i that way but not in 8i.

In 8i you can use it in SQL*Plus, you can create views, or use it in native dynamic SQL.

Which is what I thought I said when I posted it.

Daniel Morgan

Cameron Hutchison wrote:

> 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 Thu Feb 21 2002 - 11:30:27 CST