Re: CASE strucrure in PL/SQL

From: W.Breitling <member28455_at_dbforums.com>
Date: Wed, 04 Jun 2003 21:11:26 +0000
Message-ID: <2959876.1054761086@dbforums.com>

counterexample:

SQL> desc x1
 Name Null? Type

• -------- ------------ N1 NUMBER

SQL> select * from x1;

        N1

---

         1
         2
         3
         4
         5
         6
         7
         8
         9

10 rows selected.

SQL> update x1 set n1= (case when mod(n1,2)=0 then n1+10 else n1 end);

10 rows updated.

SQL> select * from x1;

        N1

---

        10
         1
        12
         3
        14
         5
        16
         7
        18
         9

10 rows selected.

The original poster did not specify the Oracle version. Prior to Oracle 9i, PL/SQL has its own sql engine which does not understand everything the sql engine does, e.g. CASE, analytical function, and a few more things. If you want to use those prior to Oracle9i, you have to imbed them in dynamic sql, which is not parsed by pl/sql but by the sql engine.

Originally posted by Jaap W . Van Dijk
> On Wed, 4 Jun 2003 09:45:59 +0200, "Onismus Radebe"
> wrote:
>
> ...
> >Bob I got the syntax from a Student Guide for Oracle 9i and the
> server that
> >I'm using is 8i I think that might be the reason causing all of
> this. I
> >could have used the IF structure because I'm lazy to type I
> decided to use
> >CASE. We 'll be moving to Oracle 9i in a month until then I 'll
> have to use
> >the IF --- THEN ---ELSIF---ELSE---END IF structure.
> ...
>
> From the Oracle 9i SQL Reference Manual:
>
> "CASE expressions let you use IF ... THEN ... ELSE logic in SQL
> statements"
>
> So *not* in an assignment statements as in your example.
>

Jaap.

--
Posted via http://dbforums.com

Received on Wed Jun 04 2003 - 16:11:26 CDT