loop in sqlplus

[atarrqis]

Oracle 10g and SQLPlus
I have customer orders with multiple lines of Part A – a top level assembly.

The user enters the customer order # and I’m using CONNECT_BY_PRIOR to pull a simple indented bill of material for Part A and finding the total qty needed of each part based on the sales qty.

SELECT
lpad(' ',3*(level-1))||LEVEL AS indented,
m.component_part, (m.qty_per_assembly * c.qty) AS qty_per_assembly,
FROM manuf_structure m, cust_ord_qty c
WHERE alternative_no = '*'
AND c.order_no = ‘&ENTER_ORDER_NO’
CONNECT BY PRIOR m.component_part = m.part_no
AND PRIOR m.contract = m.contract
AND m.eff_phase_in_date<= SYSDATE
START WITH m.part_no = c.part_no
AND m.eff_phase_in_date <= SYSDATE
AND nvl(m.eff_phase_out_date, SYSDATE) >= SYSDATE

This works fine but I may have 2 different sales parts on the same order - A & B.

I need to loop through this for each part found in cust_ord_qty. I think I need to loop through this but everything I've tried fails.

select order_no, count(part_no) AS LOOP_QTY
from ifsinfo.cust_ord_qty
where order _no = ‘&ENTER_ORDER_NO’
group by order_no;

How can I do this an get all of the lines to output to file?

 

[taupirho]

Don't you need to group by part_no also?

select order_no, part_no, count(part_no) AS LOOP_QTY
from ifsinfo.cust_ord_qty
where order _no = ‘&ENTER_ORDER_NO’
group by order_no,part_no;






In order to understand recursion, you must first understand recursion.

 

[atarrqis]

Yes, thanks, but I still need to know how to utilize this info to loop through.

 

[taupirho]

You need to provide table creation statements, insert statements that provide a minimum set of input data and your expected output


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Yes, atarrgis, as Taupirho mentioned, we'll be happy to suggest some working solutions if you provide us with "CREATE TABLE..." and "INSERT INTO..." statements that will give us a working mini-set of data and a sample of what you would like the output from your data to look like.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.