Larry, that's brilliant. That solution hadn't occurred to
me.
On Sun, 24 Mar 2002 20:53:19 -0800, Larry Elkins wrote:
>
>The easy way around it is to use Native Dynamic SQL.
>
> 1 DECLARE
> 2 dual_message VARCHAR2(20);
> 3 BEGIN
> 4 Execute Immediate
> 5 'SELECT CASE
> 6
I think the case expression is supported in PL/SQL in 9i. prior to 9i,
the pl/sql and sqlplus engine are separated. CASE just supported in
sqlplus engine not in pl/sql engine.
Joan
"CHAN Chor Ling Catherine (CSC)" wrote:
>
> Hi Jonathan,
>
> I am using Oracle8i Enterprise Edition Release 8.1.6
age-
> From: Nandu Garg [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, March 25, 2002 10:13 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: RE: CASE under Oracle8i
>
> Hi
>
> I was not knowin about CASE functionality so was just trying
> curiously following
Hi
I was not knowin about CASE functionality so was just trying
curiously following thing. Results are also below.
SQL> select case when mgr is null then ename end from emp;
CASEWHENMG
--
KING
CASEWHENMG
--
14 rows selected.
This gives me all 14 rows. Can I get out
Jonathan,
You are correct.
The first form works with SQL in 8i.
The second form does not work in 8i, but works in 9i.
- Kirti
-Original Message-
Sent: Sunday, March 24, 2002 9:18 PM
To: Multiple recipients of list ORACLE-L
If you run Oracle8i, and could conveniently test a couple
Jonathan,
Prior to 9i, PL/SQL has typically lagged a bit behind new SQL features and
constructs. So yes, things like the CASE statement, the analytical functions
introduced in 8.1.6, and other things aren't *directly* supported in PL/SQL
in 8i (or at least up through the latest patch sets I have,
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
Jonathan,
Oracle 8.1.7/Linux:
lpetrov@TESTDB> select * from v$version;
BANNER
Oracle8i Enterprise Edition Release 8.1.7.0.1 -
Production
PL/SQL Release 8.1.7.0.0 - Production
CORE8.1.7.0.0 Production
TNS for Linux: Versi
Hi Jonathan,
I am using Oracle8i Enterprise Edition Release 8.1.6.1.0
SQL> SET SERVEROUTPUT ON
SQL> EDIT
Wrote file afiedt.buf
1 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 IN
