Problem with passing data from one cursor to another.
Date: 21 May 2003 09:55:58 -0700
Message-ID: <c4147d77.0305210855.7c317a65_at_posting.google.com>
[Quoted] 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 Received on Wed May 21 2003 - 18:55:58 CEST