combining cursor information

[CassidyHunt]

Is there a way to combine the information from two cursors to make one cursor?

For example: (I know this could be accomplished with a single select but I have cases more complex that would be nice to do this)

 cursor cur_op is
 Select o.sequence_no, o.resource_id
 from operation o
 where o.workorder_type = 'W'
 and o.workorder_base_id = 'Q05458'
 and o.workorder_lot_id = '1'
 and o.workorder_split_id = '0'
 and o.workorder_sub_id = '0';
 
 cursor cur_op_cost is
  Select o.run_hrs, o.run_cost_per_hr, o.bur_per_hr_run
 from operation o
 where o.workorder_type = 'W'
 and o.workorder_base_id = 'Q05458'
 and o.workorder_lot_id = '1'
 and o.workorder_split_id = '0'
 and o.workorder_sub_id = '0';

being

'Some code to turn two cursors into one

'prt(information)

end;

I have been playing around with table types and fetch but they just don't seem to do the trick. So I am thinking I am going to have to come up with a cursor type. The reason for why I want to do this is that my end result is to have a store proc that will produce a single resultset of the combined cursor.

I am greatful for any help. This is has been an incrediable resource for me.

Cassidy

 

[SantaMufasa]

Cassidy,

Nothing prevents you from having these individual cursors plus a third cursor that results from a join/combination of the two queries. Is that a viable solution for you?

You may also extract (logically combine) information from the two cursors by simply referencing the expressions from each cursor, qualified by the respective cursor names.

Did I miss the point, or could one of these solutions take care of business for you?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CassidyHunt]

Second option might be feasable. This might explain it better.

Cursor #1

Operation Number Resource ID
---------------------------------


Cursor #2
Run Hours Run Cost Per hour
---------------------------------


Cursor #3
Op Num Res. ID Total Cost Some unrelated field
---------------------------------------------------------


I have done this before using a custom PL/SQL type, also using a temporary table. I would like to be able to do this by creating possibly another cursor on the fly kinda like you would using insert and update to the temporary tables.

Let me know if that explains it better.