Problem with passing data from one cursor to another.
Martyn Rankin
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@miss
WHERE typ in ('E') and moi > 20030000
and cga in (select id from cos@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@miss
WHERE typoccud in ('E') and moioccud > 20030000
and cgaoccud in (select id from cos@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
Rauf Sarwar
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;
END LOOP;
END;
/
Regards
/Rauf Sarwar
Martyn Rankin
> 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;
> END LOOP;
> END;
> /
>
> Regards
> /Rauf Sarwar
Thanks Rauf, your help enabled me to do exactly what I wanted to do.
Cheers
Martyn