Darrell Landrum wrote:
I thought case in PL/SQL was not available until 9i. I'll have to look that up.[EMAIL PROTECTED] 02/24/03 04:38PM >>>I too faced the problem of case not working in pl/sql & procedures. So I created a view. Would creating a view work for you ? -----Original Message----- Sent: Monday, February 24, 2003 3:07 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the follwoing code (in an PL/SQL package) into one table call (instead of two) select count(1) into count1 from isi.nametag where geneid=geneid1; select count(1) into count2 from isi.nametag where geneid=geneid2; The following code works in sqlplus, but not in PL/SQL: select count(case when geneid=geneid1 then 1 else null end ) into count1, count(case when geneid=geneid2 then 1 else null end ) into count2 from isi.nametag ; I have to use dynamic sql to get around this problem. But it's perofrmance is horrible. SQL> set serveroutput on SQL> declare 2 i number:=0; 3 str varchar2(200); 4 count1 number; 5 count2 number; 6 begin 7 str := 'select count(case when geneid=:x1 then 1 else null end ) , 8 count(case when geneid=:x2 then 1 else null end ) 9 from isi.nametag'; 10 for x1 in 1 .. 10 Loop 11 for x2 in 20 .. 30 Loop 12 i := i +1; 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2; 14 end loop; 15 end loop; 16 dbms_output.put_line('i =' || i); 17 end; 18 / i =110 PL/SQL procedure successfully completed. Elapsed: 00:00:10.96 SQL> declare 2 i number:=0; 3 count1 number; 4 count2 number; 5 begin 6 for x1 in 1 .. 100 Loop 7 for x2 in 200 .. 300 Loop 8 i := i +1; 9 select count(1) into count1 from isi.nametag where geneid=x1; 10 select count(1) into count2 from isi.nametag where geneid=x2; 11 end loop; 12 end loop; 13 dbms_output.put_line('i =' || i); 14 end; 15 / i =10100 PL/SQL procedure successfully completed. Elapsed: 00:00:04.06 Is there a better way to optimize the orginal code? TIA. Guang Mei -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
