Need to pass values to a SUBQUERY not sure of the best way to do this.
At this point I using FETCH-to retrieve records and I have inserted a subquey inside the FETCH but that is not working (see below).
Or should I have created a TABLE and use the data in the sub query with a FOR LOOP?
Can anyone suggest a better way to do this?
My goal create a query same and use the values from this query on a sub query.
DECLARE
CURSOR c_oc is
select a.bill_sequence_id, b.component_item_id
from bom_structures_b a, bom_components_b b
WHERE a.assembly_item_id = item_id
AND b.bill_sequence_id = a.bill_sequence_id
AND b.bom_item_type ='2';
c_sc c_oc%rowtype;
BEGIN
OPEN c_oc; -- CURSOR WORKS FINE WITHOUT SUBQUERY
LOOP
FETCH c_oc INTO c_sc.bill_sequence_id, c_sc.component_item_id;
DBMS_OUTPUT.PUT_LINE(c_sc.bill_sequence_id||' '||c_sc.component_item_id);
EXIT WHEN c_oc%notfound;
END LOOP;
CLOSE c_oc;
END;
/
----------------------------- CURSOR DOES NOT WORK WITH SUBQUERY -----------------------------------------------
OPEN c_oc;
LOOP
FETCH c_oc INTO c_sc.bill_sequence_id, c_sc.component_item_id;
FROM c_sc.component_item_id, (SELECT a.assembly_item_id, a.organization_id, a.bill_sequence_id, b.bom_item_type, b.bill_sequence_id, b.component_item_id
FROM bom_structures_b a, bom_components_b b
WHERE a.assembly_item_id = c_sc.component_item_id
AND a.organization_id = '207'
AND b.bill_sequence_id = a.bill_sequence_id
AND b.bom_item_type = '4'; )
DBMS_OUTPUT.PUT_LINE(c_sc.bill_sequence_id||' '||c_sc.component_item_id);
EXIT WHEN c_oc%notfound;
END LOOP;
CLOSE c_oc;
END;
Error report:
ORA-06550: line 28, column 4:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
( begin case declare end exit for goto if loop mod null
pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
The symbol "delete" was substituted for "FROM" to continue.
ORA-06550: line 28, column 31:
PLS-00103: Encountered the symbol "," when expecting one of the following:
. @ ; return returning <an identifier>
<a double-quoted delimited-identifier> partition subpartition
where
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action: