|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-25 : 19:02:57
|
Melanie writes "Right now I am using 3 cursors in a query to try to select a finished good part number, then based on that number I select all the sub assembly part numbers that make up that finished good part number. I do a calculation on that and then update lotsizes for the finished good numbers. The problem that I am having is that those sub assembly part numbers can break down 1 to 2 more times with their own sub assembly part numbers which I would then have to do a calculation on that and update the lotsizes for where that part number is manufactured. Although I am using cursors for row by row operations I think that I could possibly use a nested iteration or a recursive loop to keep looping through the finished good part numbers and then loop through the sub assembly numbers. I have enclosed the cursors that I am using. Any help would be greatly appreciated.
thanks for any help in advance. (Note: I am using Microsoft SQL Server 6.50 - 6.50.415 and windows nt service pack 6.
********beginning of my query*************************
set nocount on
--Temporary table to hold finished good lotsizes from lotsize report
create table #temp_fg(asm_no varchar (30) not null, part_no varchar(30) not null, location varchar(20) not null,
src_loc varchar(20) not null, lot_size decimal(20) not null,
pass_status varchar(1) null)
insert #temp_fg
select part_no, '0', location, src_loc, lot_size, "0"
from ATC_fg_lotsize
where src_loc <> ' '
--select * from #temp_fg
--Temporary table to hold the calculated lot_sizes after retrieving the qty from what_part table
create table #temp_build(asm_no varchar(30) not null, part_no varchar (30) not null, location varchar(30) not null,
src_loc varchar(20) not null, lot_size decimal(20, 8) not null)
--Temporary table to hold original lotsize for locations that will be adjusted
--create table #temp_lotsize(part_no varchar(30) not null, location varchar(30) not null,
--src_loc varchar(20) not null, lot_size decimal(20, 8) not null)
--Insert original lotsizes from ATC_inv_list
delete from temp_lotsize
insert temp_lotsize
select part_no, location, src_loc, lot_size
from ATC_inv_list
--Cursor that holds the scrolls through the part_nos from the #temp_fg table
declare fg_parts cursor
for select asm_no, part_no, location, src_loc, lot_size
from #temp_fg
open fg_parts
declare @asm_no varchar(30), @part_no varchar(30), @location varchar(30), @src_loc varchar(30), @lot_size decimal(20)
fetch fg_parts
into @asm_no, @part_no , @location, @src_loc, @lot_size
while @@fetch_status =0
begin
select @asm_no, @part_no, @location, @src_loc, @lot_size
--end
--RESULTS
--01100504, 0, BM, GB
--Cursor that collects the part breakdown from the what_part table based on the asm_no from the #temp_build table
declare build_plan cursor
for select asm_no, part_no, location, qty
from what_part
where asm_no = @asm_no and uom <> 'HR' and
(location = @src_loc or location ='ALL')
declare @bp_asm_no varchar(30), @bp_part_no varchar(30), @bp_location varchar(10), @bp_qty decimal(20, 8)
open build_plan
fetch build_plan into @bp_asm_no, @bp_part_no, @bp_location, @bp_qty
while @@fetch_status = 0
begin
--select @bp_asm_no, @bp_part_no, @bp_location, @bp_qty
--****RESULTS
--01100504, 00021006, ALL, 0.05000000
--01100504, 09900011, ALL, 1.00000000
insert into ATC_temp_build(asm_no, part_no, location, src_loc, lot_size)values(@bp_asm_no, @bp_part_no, @bp_location, @src_loc, (@lot_size * @bp_qty))
select part_no, location, src_loc, lot_size
from temp_lotsize
where part_no = @bp_part_no
d |
|
Nested Iterations/Recursive Loops