Home » SQL & PL/SQL » SQL & PL/SQL » combining output of two cursors.
combining output of two cursors. [message #265619] Thu, 06 September 2007 14:06 Go to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Dear All,

I would like to combine the output of two cursors.

example

    cursor c1 (v_empno in number) is 
       select * from emp_new
       where empno = v_empno ;

      cursor c2 (v_deptno in number) is 
      select * from emp_old
       where deptno =v_deptno;


how can i combine the output of these two cursors data into another cursor. is there any way to do that, pls help me.

regards
Kumar.

[Updated on: Thu, 06 September 2007 14:08]

Report message to a moderator

Re: combining output of two cursors. [message #265620 is a reply to message #265619] Thu, 06 September 2007 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only way is:
cursor c3 is 
  select * from emp_new;
  union all
  select * from emp_old;

Regards
Michel
Re: combining output of two cursors. [message #265621 is a reply to message #265620] Thu, 06 September 2007 14:10 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Michel,

thanks for the reply, but the parameter values of these cursors are coming from another cursors in plsql block.

regards
Sai Kumar.
Re: combining output of two cursors. [message #265625 is a reply to message #265621] Thu, 06 September 2007 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same thing on your modified cursors:
cursor c1 (v_empno in number, v_deptno in number) is 
  select * from emp_new
  where empno = v_empno
  union all
  select * from emp_old
  where deptno =v_deptno;

Regards
Michel
Re: combining output of two cursors. [message #265898 is a reply to message #265625] Fri, 07 September 2007 14:30 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

thanks Michel, it worked. But i need one more help with loops. as i am really new to plsql. need you people help.

I have totally 3 cursors as

cursor c1 is 
select col1 from emp where desg = 'MGR';

cursor c2 is
select col1 from emp where desg <> 'MGR';

cursor c3 (v_col1 in number,v_col2 in number) is
select * from emp_old 
where col1 = v_col1
UNION ALL
select * from emp_old
where col1 = v_col2;


i would like to generate bonus for the employees came out from cursor3 i.e with loops. as first two cursor values to be used in cursor3.

can any one pls guide me.

regards
Sai Kumar.
Re: combining output of two cursors. [message #265901 is a reply to message #265898] Fri, 07 September 2007 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I don't understand what you have in input, what you want, where does it come from and so on.

Regards
Michel
Re: combining output of two cursors. [message #265905 is a reply to message #265901] Fri, 07 September 2007 15:28 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

One of the sql of cursor c3 is already exists in one procedure of the application which i am developing. Now new requirement is with new cursor c3 which we have written with union all.

the code in existing procedure as...


BEGIN
OPEN c3;
LOOP
BEGIN
  FETCH c3 INTO ccm;
  EXIT WHEN c3%NOTFOUND;
  doing some thing....
  EXCEPTION
  WHEN OTHERS THEN
  update some thing....
  END;
END LOOP;
END; 


as we are using parameters for the cursor c3. while i am looping with the other two cursors. for me data is coming wrong.
In one line, i would like to execute cursor c3 with the parameters values of cursor c1 & c2.

regards
Sai Kumar.
Re: combining output of two cursors. [message #265908 is a reply to message #265905] Fri, 07 September 2007 15:57 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

this the code i have ... it is returining wrong number of rows than expected.

  declare
 cursor c1 is
 select b.custno from cust_bus_details a,shipto_cust b
 where a.interface_table in ('E','W')
 and a.czifsystem ='MF'
 and a.cziftype ='CM'
 and a.czifref = b.custno
 and b.custnobill not in
 (select custnobill from shipto_cust
 minus
 select custnobill from shipto_cust where custnobill = custno);
 
cursor c3 is
 select b.custno from cust_bus_details a,shipto_cust b
 where a.interface_table in ('E','W')
 and a.czifsystem ='MF'
 and a.cziftype ='CM'
 and a.czifref = b.custno
 and b.custnobill in
 (select custnobill from shipto_cust
 minus
 select custnobill from shipto_cust where custnobill = custno);
 
cursor c2  (v_custno in number, v_custno1 in number)  is
 (select decode(buy.custnobill,buy.custno,'0','1') as shipto_only,
 buy.custnobill,
 buy.custno,
 to_number(lpad(decode(replace(translate(buy.custnobill,
  '0123456789','0000000000'),
   '0',null),null,buy.custnobill,null),7,'0')) as billno,
 to_number(lpad(decode(replace(translate(buy.custno,
  '0123456789','0000000000'),
   '0',null),null,buy.custno,null),7,'0'))        as buyno,
 buy.name1,
 buy.name2,
 buy.comment1,
 buy.storeno,
 ext.termsdel,
 bus.cubusalesman,
 bus.compno,
 bil.termspay,
 bil.currency
 from shipto_cust buy,
 billto_cust bil,
 cust_bus_details bus,
 czshipto_custext1 ext,
 cust_bus_details ift
 where
 ift.interface_table in ('E','W')
    and ift.czifsystem = 'MF'
    and ift .cziftype = 'CM'
    and buy.custno = ift.czifref
    and bus.custno = buy.custno
    and bus.compno = buy.compno
    and bil.custnobill = buy.custnobill
    and ext.custno = buy.custno
   and buy.custno = v_custno
 UNION ALL
 select
  decode(buy.custnobill,buy.custno,'0','1') as shipto_only,
 buy.custnobill,
 --buy.custno,
 decode(buy.custno,buy.custnobill,buy.custno,buy.custnobill) custno,
 to_number(lpad(decode(replace(translate(buy.custnobill,
  '0123456789','0000000000'),
   '0',null),null,buy.custnobill,null),7,'0')) as billno,
 to_number(lpad(decode(replace(translate(buy.custno,
  '0123456789','0000000000'),
   '0',null),null,buy.custno,null),7,'0'))        as buyno,
 bil.name1,
 bil.name2,
 buy.comment1,
 buy.storeno,
 ext.termsdel,
 bus.cubusalesman,
 bus.compno,
 bil.termspay,
 bil.currency
 from shipto_cust buy,
 billto_cust bil,
 cust_bus_details bus,
 czshipto_custext1 ext,
 cust_bus_details ift
 where
 ift.interface_table in ('E','W')
    and ift.czifsystem = 'MF'
    and ift .cziftype = 'CM'
    and buy.custno = ift.czifref
    and bus.custno = buy.custno
    and bus.compno = buy.compno
    and bil.custnobill = buy.custnobill
    and ext.custno = buy.custno
    and buy.custno = v_custno1);
 main c1%rowtype;
 main1 c3%rowtype;
 sub  c2%rowtype;
 begin
    open c1;
    loop
    fetch c1 into main;
    exit when c1%notfound;
    dbms_output.put_line (main.custno);
            begin
                 if c3%isopen then
                 close c3;
                 end if;
            open c3;
            loop
            fetch c3 into main1;
            exit when c3%notfound;
            dbms_output.put_line (main1.custno);
            end loop;
            end;    
    end loop;
 end;

[Updated on: Fri, 07 September 2007 16:09]

Report message to a moderator

Re: combining output of two cursors. [message #265916 is a reply to message #265619] Fri, 07 September 2007 17:59 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>this the code i have ... it is returning wrong number of rows than expected.

If you say so.
Since we (TINW) don't have your data & don't know what you expect,
it is impossible to provide you any useful guidance.

Re: combining output of two cursors. [message #265925 is a reply to message #265908] Sat, 08 September 2007 00:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read up about cursor for loops.
Opening and fetching cursors manually is not the preferred way of doing this. If however you will stick to this crappy method, close the cursor after the loop. This will remove the need for the %isopen check and will leave no cursors open.

Furthermore: use readable and logical names. a and b as table-alias will force you to scroll back to your table list every time. Same for main and main1. Which one contains which cursors contents? You have to get back to the declaration part to be sure.

Too bad you don't use c2, as you seem to have put so much work in it.
Re: combining output of two cursors. [message #265969 is a reply to message #265925] Sat, 08 September 2007 02:51 Go to previous message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Hi Frank,

I am using c3 output as second parameter of c2.

regards
Kumar.

[Updated on: Sat, 08 September 2007 03:44]

Report message to a moderator

Previous Topic: Need oracle utilities and pl/sql packags for converting .CSV files as ZIP file and send as a mail.
Next Topic: Purchased Items summary
Goto Forum:
  


Current Time: Tue Dec 06 02:49:54 CST 2016

Total time taken to generate the page: 0.07131 seconds