Re: Problem with passing data from one cursor to another.

mgf_rankin_at_yahoo.com (Martyn Rankin) wrote in message news:<c4147d77.0305210855.7c317a65_at_posting.google.com>...
> Hi, I am trying to write a script to allow me to compare data in two
> databases to check that the two sets of data are consistent. I want to
> use PL/SQL so that I can take each division in turn and pass it into
> the following two SQL queries, and incorporate it all in one script
> that will output the results to a file which I can then manipulate to
> find any discrepencies in data:
>
> (a) SELECT SUM(open_co) oc from transactions
> WHERE div like 'AS';
>
> and
>
> (b) SELECT SUM( cud-cuc) oc from occud_at_miss
> WHERE typ in ('E') and moi > 20030000
> and cga in (select id from cos_at_miss connect by prior id = pid
> start with id = 'AS');
>
> a third query "SELECT div FROM divisions" gives me the divisions, the
> first of which is 'AS' - I then want to put each subsequent division
> in place of the value 'AS' in queries (a) & (b).
>
> I have spent a lot of time trying to find the best way to do this. For
> example I started with a cursor which incorporated the first query and
> also the 'select division' query, and a loop to dislay the results
> e.g.
>
> (c) CURSOR divisions_cursor IS
> SELECT d.div, SUM(open_co) oc
> FROM divisions d, transactions t
> WHERE t.div = d.div
> GROUP by d.div;
> divisions_record divisions_cursor%ROWTYPE;
>
> BEGIN
> OPEN divisions_cursor;
> LOOP
> FETCH divisions_cursor INTO divisions_record;
> DBMS_OUTPUT.PUT_LINE (divisions_record.div || ', ' ||
> divisions_record.oc);
> EXIT WHEN divisions_cursor%NOTFOUND;
> END LOOP;
> END;
>
> which gives me the perfect results for all the divisions for query
> (a). But it is when I want to do both at once that I am encountering
> problems. Could somebody suggest how I can step through each division
> one by one by SELECT div FROM divisions, put the division into a
> variable in memory and then pass it to the 2 main queries.
> does not give me the correct results.
>
> I would appreciate any help, I am getting really confused. I would
> quite happily split the task into two and have two scripts, one for
> query (a) and one for query (b), because like I managed to get perfect
> results for query (a) with the script (c). But trying to do a similar
> thing with (b) does not seem to work:
>
> CURSOR occud_cursor IS
> SELECT
> SUM( cudoccud-cucoccud) oc from occud_at_miss
> WHERE typoccud in ('E') and moioccud > 20030000
> and cgaoccud in (select id from cos_at_miss connect by prior id =
> pid start with id = 'divisions_record.div');
> occud_record occud_cursor%ROWTYPE;
>
> Could I use a cursor such as:
>
> DECLARE
> CURSOR divisions_cursor IS
> SELECT div from divisions;
>
> to loop around the divisions, and for each row put the value into a
> variable e.g. v_div, and then substitute it into the SELECT statement
> above e.g. .... start with id = v_div.
>
> I know I am rambling on with this message, but somewhere in my
> development I am going wrong, and I am unsure of the best solution to
> my problem. I don't like to ask straight out for a solution but I am
> being driven to despair at the moment!
>
> Thanks for your time in advance,
>
> Martyn

[Quoted] If I understand correctly from your post... you want to take a value from one cursor and pass it to a second cursor which can then be used in the where clause of the second cursor???. You can do that e.g.

DECLARE
   CURSOR parent_ IS

      SELECT dept_id FROM department;
   --
   CURSOR child_ (dept_id_ IN VARCHAR2) IS

      SELECT emp_id FROM employees WHERE dept_id = dept_id_; BEGIN
   FOR rec_parent_ IN parent_ LOOP

      FOR rec_child_ IN child_ (rec_parent_.dept_id) LOOP
         ....
      END LOOP;

Regards
/Rauf Sarwar Received on Thu May 22 2003 - 08:10:08 CEST