RE: ** is there PL/SQL for case

[Igor Neyman]

To overcome this in PL/SQL you can run sql, which includes “case”, as “dynamic sql”:   EXECUTE IMMEDIATE ‘SELECT …’   Igor Neyman, OCP DBA [EMAIL PROTECTED]     -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Guang Mei Sent: Thursday, August 07, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: ** is there PL/SQL for case   Below is an example (with Oracle 8173), "case" works in sql, not in pl/sql.   Guang     [EMAIL PROTECTED]> create table t1  (name varchar2(30),  salary number );   Table created.   [EMAIL PROTECTED]> insert into t1 (name,salary) values ('Bill',   1000);   1 row created.   [EMAIL PROTECTED]> insert into t1 (name,salary) values ('George', 2000);   1 row created.   [EMAIL PROTECTED]> insert into t1 (name,salary) values ('Gore',   3000);   1 row created.   [EMAIL PROTECTED]> insert into t1 (name,salary) values ('Dick',   4000);   1 row created.   [EMAIL PROTECTED]> commit;   Commit complete.   [EMAIL PROTECTED]> select * from t1;   NAME                               SALARY ------------------------------ ---------- Bill                                 1000 George                               2000 Gore                                 3000 Dick                                 4000   [EMAIL PROTECTED]> select count(case when salary < 2000 then 1 else null end)  poor,        count(case when salary between 2000 and 3000 then 1 else null end) middle_class,        count(case when salary > 3000 then 1 else null end)  rich  from t1;             POOR MIDDLE_CLASS       RICH ---------- ------------ ----------          1            2          1   [EMAIL PROTECTED]> declare   c1 number;   c2 number;   c3 number; begin   select count(case when salary < 2000 then 1 else null end)  poor,          count(case when salary between 2000 and 3000 then 1 else null end) middle_class,          count(case when salary > 3000 then 1 else null end)  rich    into c1,c2,c3      from t1; end; /     select count(case when salary < 2000 then 1 else null end)  poor,                * ERROR at line 6: ORA-06550: line 6, column 16: 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> avg count current distinct max min prior sql stddev sum unique variance execute forall time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string>   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of A Joshi Sent: Thursday, August 07, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: ** is there PL/SQL for case Guang,   Thanks for your help. Do you have an example you can send me. I thought whatever one can do in sql one can do in pl/sql. meaning sql is a subset of pl/sql. Correct me if i am wrong. Thank You. Guang Mei <[EMAIL PROTECTED]> wrote: I am not sure in 9i. But in 8i I think you can use "case" in sql but not in pl/sql. You have to use if elsif in pl/sql.   Guang  -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of A Joshi Sent: Thursday, August 07, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: ** is there PL/SQL for case Hi,   Is there a statement in pl/SQL like case or is if elsif the only way. Meaning if I need to transalate state depending on input number as follows :   1 - CA 2 - OR 3 - WA 4 - AR   Can I have one statement like case 'state# :   1: state := 'CA' 2: state := 'OR'   etc.   or do i have to do :   IF state# = 1 THEN   state := 'CA'; ELSIF state# = 2 THEN   state := 'OR';   etc   Thank You.     Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software