Toad World® Forums

Using value of a column in first cursor in second


#1

FOR vll_use IN cur_vll
LOOP
v_item := vll_use.item;
v_dest := vll_use.dest;
v_aggqty := vll_use.aggsupqty;

FOR sk_lir IN cur_sk_lir
LOOP
v_aggqty := v_aggqty - sk_lir.needqty;

CASE
WHEN v_aggqty > 0
THEN
UPDATE suppordersku sk
SET status = 2
WHERE sk.item = v_item
AND sk.loc = v_dest
AND sk.needarrivdate = sk_lir.needarrivdate;
DBMS_OUTPUT.PUT_LINE ('aggregate quantity is ’ || v_aggqty);

WHEN v_aggqty = 0
THEN
UPDATE suppordersku sk
SET status = 2
WHERE sk.item = v_item
AND sk.loc = v_dest
AND sk.needarrivdate = sk_lir.needarrivdate;
DBMS_OUTPUT.PUT_LINE ('aggregate quantity is ’ || v_aggqty);

ELSE
UPDATE suppordersku sk
SET needqty = ABS (v_aggqty)
WHERE sk.item = v_item
AND sk.loc = v_dest
AND sk.needarrivdate = sk_lir.needarrivdate;
DBMS_OUTPUT.PUT_LINE ('aggregate quantity is ’ || v_aggqty);

END CASE;
END LOOP;

FOR sk_gui IN cur_sk_gui
LOOP
v_aggqty := v_aggqty - sk_gui.needqty;

How to avoid entering second loop i.e. cur_sk_gui if value of v_aggqty =0 after 1st loop???