Cursor Fetch Duplication

Thanks in Advance,
I have had a situation in a cursor that I built (see example 1 below) where
I discovered that the first fetch was not being inserted into my table and
the last one was going in twice. After consulting BOL, I figured out why
(1. the variables were loaded with the second fetch before being asked to do
anything with the first fetch, 2. the fetch always occurs if the last fetch
was successful). I seemed to have a work aroung in example 2 below, but it
does not appear to be very elegant. Can someone show me something in cursor
programming that is a standard, elegant way of solving the problem that I
had?
Kind Regards,
Mark Simmerman
SQL Learner
Napa, CA
Example 1
--TAKES THE INPUT OF FINISHED GOODS AND QUANTITIES, CALCULATES GROSS
REQUIREMENTS FOR COMPONENTS, AND
--EXPORTS THE RESULTS TO A TABLE
-- ****************************************
**********************************
***
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--Deletes a previous table if present.
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'GrossOutput')
DROP TABLE GrossOutput
GO
--Creates a table (GrossOutput) to receive the results of the
sr_GrossRequirements procedure.
CREATE TABLE GrossOutput
(
[gross_item_output] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[gross_qty_output] [decimal](15, 6) NULL ,
[gross_total_output] [decimal](15, 6) NULL ,
[gross_layer_output] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
)
GO
--Sets up a cursor to select each row from GrossInput, pass it to
sr_GrossRequirements, and insert the
--resulting row into table GrossOutput.
Declare @.sysitem varchar(15), @.sysqty dec(15,6)
DECLARE Name_Cursor CURSOR FOR
SELECT gross_item_input, gross_qty_input FROM GrossInput WHERE
gross_qty_input <> 0
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
SET @.sysitem = 0
SET @.sysqty = 0
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor
GO
SELECT 'Finished All Tables'
GO
--SET STATISTICS IO OFF
--SET STATISTICS TIME OFF
Example 2
--TAKES THE INPUT OF FINISHED GOODS AND QUANTITIES, CALCULATES GROSS
REQUIREMENTS FOR COMPONENTS, AND
--EXPORTS THE RESULTS TO A TABLE
-- ****************************************
**********************************
***
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--Deletes a previous table if present.
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'GrossOutput')
DROP TABLE GrossOutput
GO
--Creates a table (GrossOutput) to receive the results of the
sr_GrossRequirements procedure.
CREATE TABLE GrossOutput
(
[gross_item_output] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[gross_qty_output] [decimal](15, 6) NULL ,
[gross_total_output] [decimal](15, 6) NULL ,
[gross_layer_output] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
)
GO
--Sets up a cursor to select each row from GrossInput, pass it to
sr_GrossRequirements, and insert the
--resulting row into table GrossOutput.
Declare @.sysitem varchar(15), @.sysqty dec(15,6)
DECLARE Name_Cursor CURSOR FOR
SELECT gross_item_input, gross_qty_input FROM GrossInput WHERE
gross_qty_input <> 0
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
SET @.sysitem = 0
SET @.sysqty = 0
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor
GO
SELECT 'Finished All Tables'
GO
--SET STATISTICS IO OFF
--SET STATISTICS TIME OFFMark (msimmer@.mezzetta.com) writes:
> I have had a situation in a cursor that I built (see example 1 below)
> where I discovered that the first fetch was not being inserted into my
> table and the last one was going in twice. After consulting BOL, I
> figured out why (1. the variables were loaded with the second fetch
> before being asked to do anything with the first fetch, 2. the fetch
> always occurs if the last fetch was successful). I seemed to have a
> work aroung in example 2 below, but it does not appear to be very
> elegant. Can someone show me something in cursor programming that is a
> standard, elegant way of solving the problem that I had?
I always write cursor loops as:
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
OPEN
WHILE 1 = 1
BEGIN
FETCH mycur INTO
IF @.@.fetch_status <> 0
BREAK
-- action comes here
END
DEALLOCATE mycur
The chief reason for this that having two FETCH causes a maintance
problem. You add one more column to the query, but you forget to
change the second FETCH which may be 100 lines down, if it is a long
loop. This can be quite nasty, because the it's not until you get
to the second row, that the loop fails.
By only having one FETCH, you avoid this problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You cant avoid fetch duplication , but you still can impove your cursors:
in this example only one insert statement is used (instead of 2)
Declare @.sysitem varchar(15), @.sysqty dec(15,6)
DECLARE Name_Cursor CURSOR FOR
SELECT
gross_item_input,
gross_qty_input
FROM GrossInput
WHERE gross_qty_input <> 0
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor
Also in Query Analyzer (in object browser) exists such tab - Templates ,
where you can see templates
how to write cursors and also others DB objects.
Also in is not very good practice to check @.@.FETCH_STATUS only with 0 , you
should chekck it with -1 and -2
in that case previous example should be rewriten
Declare @.sysitem varchar(15), @.sysqty dec(15,6)
DECLARE Name_Cursor CURSOR FOR
SELECT
gross_item_input,
gross_qty_input
FROM GrossInput
WHERE gross_qty_input <> 0
OPEN Name_Cursor
FETCH NEXT FROM Name_Cursor
INTO @.sysitem, @.sysqty
WHILE @.@.FETCH_STATUS != -1
BEGIN
IF (@.@.FETCH_STATUS != -2)
BEGIN
INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
END
FETCH NEXT FROM Name_Cursor INTO @.sysitem, @.sysqty
END
CLOSE Name_Cursor
DEALLOCATE Name_Cursor|||Thanks, Erland.
The "break" has me concerned, but I will attempt it .
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976B78CA5266Yazorman@.127.0.0.1...
> Mark (msimmer@.mezzetta.com) writes:
> I always write cursor loops as:
> DECLARE mycur INSENSITIVE CURSOR FOR
> SELECT ...
> OPEN
> WHILE 1 = 1
> BEGIN
> FETCH mycur INTO
> IF @.@.fetch_status <> 0
> BREAK
> -- action comes here
> END
> DEALLOCATE mycur
> The chief reason for this that having two FETCH causes a maintance
> problem. You add one more column to the query, but you forget to
> change the second FETCH which may be 100 lines down, if it is a long
> loop. This can be quite nasty, because the it's not until you get
> to the second row, that the loop fails.
> By only having one FETCH, you avoid this problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||It works great. I wonder why I never saw this technique used in Ken
Henderson's Guru's guide to T-SQL?
By the way, could this operation be performed without a cursor more
efficiently?
Thanks,
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976B78CA5266Yazorman@.127.0.0.1...
> Mark (msimmer@.mezzetta.com) writes:
> I always write cursor loops as:
> DECLARE mycur INSENSITIVE CURSOR FOR
> SELECT ...
> OPEN
> WHILE 1 = 1
> BEGIN
> FETCH mycur INTO
> IF @.@.fetch_status <> 0
> BREAK
> -- action comes here
> END
> DEALLOCATE mycur
> The chief reason for this that having two FETCH causes a maintance
> problem. You add one more column to the query, but you forget to
> change the second FETCH which may be 100 lines down, if it is a long
> loop. This can be quite nasty, because the it's not until you get
> to the second row, that the loop fails.
> By only having one FETCH, you avoid this problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I appreciate your insight on using @.@.Fetch_Status != -1 or -2. I will
investigate it further.
I recommend reading Erland's solution. His method of not duplicating the
fetch appears to work for me. Do you see any problems with it?
Thanks,
Mark
"Artur" <Artur@.discussions.microsoft.com> wrote in message
news:3B289CA6-1516-4A6B-B1F7-60A2FC0EAB8E@.microsoft.com...
> You cant avoid fetch duplication , but you still can impove your cursors:
> in this example only one insert statement is used (instead of 2)
> Declare @.sysitem varchar(15), @.sysqty dec(15,6)
> DECLARE Name_Cursor CURSOR FOR
> SELECT
> gross_item_input,
> gross_qty_input
> FROM GrossInput
> WHERE gross_qty_input <> 0
> OPEN Name_Cursor
> FETCH NEXT FROM Name_Cursor
> INTO @.sysitem, @.sysqty
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
> FETCH NEXT FROM Name_Cursor
> INTO @.sysitem, @.sysqty
>
> END
> CLOSE Name_Cursor
> DEALLOCATE Name_Cursor
>
> Also in Query Analyzer (in object browser) exists such tab - Templates ,
> where you can see templates
> how to write cursors and also others DB objects.
> Also in is not very good practice to check @.@.FETCH_STATUS only with 0 ,
> you
> should chekck it with -1 and -2
> in that case previous example should be rewriten
> Declare @.sysitem varchar(15), @.sysqty dec(15,6)
> DECLARE Name_Cursor CURSOR FOR
> SELECT
> gross_item_input,
> gross_qty_input
> FROM GrossInput
> WHERE gross_qty_input <> 0
> OPEN Name_Cursor
> FETCH NEXT FROM Name_Cursor
> INTO @.sysitem, @.sysqty
> WHILE @.@.FETCH_STATUS != -1
> BEGIN
> IF (@.@.FETCH_STATUS != -2)
> BEGIN
> INSERT GrossOutput EXEC sr_GrossRequirements @.sysitem, @.sysqty
> END
> FETCH NEXT FROM Name_Cursor INTO @.sysitem, @.sysqty
> END
> CLOSE Name_Cursor
> DEALLOCATE Name_Cursor|||Mark wrote:
> By the way, could this operation be performed without a cursor more
> efficiently?
>
We don't know what sr_GrossRequirements does so we can't answer that
for sure. My intuition says that the answer will be a big YES. If you
didn't already know the answer to that question then you probably
shouldn't have embarked on writing the cursor to start with.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I can make a few general comments on this.
loop while < true condition>
read
if eof
break out of loop
end if
process
end of loop
The advantage to this one that it is simpler to read and maintain. The
divantage is that you have two condition checks instead of one each
cycle. I am not sure this is a significant now as it was 20 years ago.
read -- priming read
loop until eof
process
read -- trailing read
end of loop
This is almost a simple for me to read, but then I am used to it.
There is only one conditional check per cycle.
Control break logic, especially at eof, is more complicated in the
second format than the first.
Hope this makes some sense.
Payson
Mark wrote:
> I appreciate your insight on using @.@.Fetch_Status != -1 or -2. I will
> investigate it further.
> I recommend reading Erland's solution. His method of not duplicating the
> fetch appears to work for me. Do you see any problems with it?
> Thanks,
> Mark
> "Artur" <Artur@.discussions.microsoft.com> wrote in message
> news:3B289CA6-1516-4A6B-B1F7-60A2FC0EAB8E@.microsoft.com...|||Thank you for your snooty response.
Here is the code for sr_GrossRequirements. The database I am working with
will not allow alteration to the schema and still allow the accounting
system to work. It works with an old style BOM table.
CREATE PROCEDURE dbo.sr_GrossRequirements
@.gross_item_input VARCHAR(15), @.gross_qty_input DEC(15,6)
AS
--LAYER FIVE ITEMS IN THIS TABLE
SELECT layer5_item_no AS 'comp_item_no',
layer5_qty * layer4_qty * layer3_qty * layer2_qty * layer1_qty * qty AS
'qty_per_parent',
layer5_qty * layer4_qty * layer3_qty * layer2_qty * layer1_qty * qty *
@.gross_qty_input AS 'Total', 'Layer5' AS 'Layer'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty',
t3.comp_item_no AS 'layer3_item_no', t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'layer3_qty',
t4.comp_item_no AS 'layer4_item_no', t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'layer4_qty',
t5.comp_item_no AS 'layer5_item_no', t5.qty_per_par + (t5.qty_per_par *
t5.scrap_factor /100) AS 'layer5_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer3_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer4_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer5_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer3_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer4_qty',
t7.comp_item_no, t7.qty_per_par + (t7.qty_per_par * t7.scrap_factor /100)
AS 'layer5_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer3_qty',
t7.comp_item_no, t7.qty_per_par + (t7.qty_per_par * t7.scrap_factor /100)
AS 'layer4_qty',
t8.comp_item_no, t8.qty_per_par + (t8.qty_per_par * t8.scrap_factor /100)
AS 'layer5_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer2_qty',
t7.comp_item_no, t7.qty_per_par + (t7.qty_per_par * t7.scrap_factor /100)
AS 'layer3_qty',
t8.comp_item_no, t8.qty_per_par + (t8.qty_per_par * t8.scrap_factor /100)
AS 'layer4_qty','0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%')
e5
WHERE e5.layer5_item_no IS NOT NULL AND e5.layer5_item_no LIKE '[019]%' AND
e5.item_no IN (@.gross_item_input)
UNION
--LAYER FOUR ITEMS IN THIS TABLE
SELECT layer4_item_no AS 'comp_item_no',
layer4_qty * layer3_qty * layer2_qty * layer1_qty * qty AS
'qty_per_parent',
layer4_qty * layer3_qty * layer2_qty * layer1_qty * qty * @.gross_qty_input
AS 'Total', 'Layer4'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty',
t3.comp_item_no AS 'layer3_item_no', t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'layer3_qty',
t4.comp_item_no AS 'layer4_item_no', t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'layer4_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer3_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer4_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer3_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer4_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer3_qty',
t7.comp_item_no, t7.qty_per_par + (t7.qty_per_par * t7.scrap_factor /100)
AS 'layer4_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer2_qty',
t7.comp_item_no, t7.qty_per_par + (t7.qty_per_par * t7.scrap_factor /100)
AS 'layer3_qty','0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%')
e4
WHERE e4.layer4_item_no IS NOT NULL AND e4.layer4_item_no LIKE '[019]%' AND
e4.item_no IN (@.gross_item_input)
UNION
--LAYER THREE ITEMS IN THIS TABLE
SELECT layer3_item_no AS 'comp_item_no', layer3_qty * layer2_qty *
layer1_qty * qty AS 'qty_per_parent',
layer3_qty * layer2_qty * layer1_qty * qty * @.gross_qty_input AS 'Total',
'Layer3'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty',
t3.comp_item_no AS 'layer3_item_no', t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'layer3_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer3_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer3_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer3_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty',
t6.comp_item_no, t6.qty_per_par + (t6.qty_per_par * t6.scrap_factor /100)
AS 'layer2_qty','0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%')
e3
WHERE e3.layer3_item_no IS NOT NULL AND e3.layer3_item_no LIKE '[019]%' AND
e3.item_no IN (@.gross_item_input)
UNION
--LAYER TWO ITEMS IN THIS TABLE
SELECT layer2_item_no AS 'comp_item_no', layer2_qty * layer1_qty * qty AS
'qty_per_parent',
layer2_qty * layer1_qty * qty * @.gross_qty_input AS 'Total', 'Layer2'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty', '0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%')
e2
WHERE e2.layer2_item_no IS NOT NULL AND e2.layer2_item_no LIKE '[019]%' AND
e2.item_no IN (@.gross_item_input)
UNION
--LAYER ONE ITEMS IN THIS TABLE
SELECT layer1_item_no AS 'comp_item_no', layer1_qty * qty AS
'qty_per_parent',
layer1_qty * qty * @.gross_qty_input AS 'Total', 'Layer1'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty', '0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%')
e1
WHERE e1.layer1_item_no IS NOT NULL AND e1.layer1_item_no LIKE '[019]%' AND
e1.item_no IN (@.gross_item_input)
UNION
--LAYER 0 ITEMS IN THIS TABLE
SELECT comp_item_no AS 'comp_item_no', qty AS 'qty_per_parent', qty *
@.gross_qty_input AS 'Total', 'Layer0'
FROM (SELECT b.item_no, b.comp_item_no, b.qty_per_par +
(b.qty_per_par * b.scrap_factor /100) AS 'qty',
t1.comp_item_no AS 'layer1_item_no', t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'layer1_qty',
t2.comp_item_no AS 'layer2_item_no', t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND b.item_no LIKE '[019]%'
UNION
SELECT t1.item_no, t1.comp_item_no, t1.qty_per_par + (t1.qty_per_par *
t1.scrap_factor /100) AS 'qty',
t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par * t2.scrap_factor /100)
AS 'layer1_qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t1.item_no LIKE '[019]%'
UNION
SELECT t2.item_no, t2.comp_item_no, t2.qty_per_par + (t2.qty_per_par *
t2.scrap_factor /100) AS 'qty',
t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par * t3.scrap_factor/100)
AS 'layer1_qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t2.item_no LIKE '[019]%'
UNION
SELECT t3.item_no, t3.comp_item_no, t3.qty_per_par + (t3.qty_per_par *
t3.scrap_factor /100) AS 'qty',
t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par * t4.scrap_factor/100)
AS 'layer1_qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer2_qty'
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t3.item_no LIKE '[019]%'
UNION
SELECT t4.item_no, t4.comp_item_no, t4.qty_per_par + (t4.qty_per_par *
t4.scrap_factor /100) AS 'qty',
t5.comp_item_no, t5.qty_per_par + (t5.qty_per_par * t5.scrap_factor /100)
AS 'layer1_qty', '0', 0
FROM BMPRDSTR_SQL b LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t1 ON b.comp_item_no
= t1.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t2 ON
t1.comp_item_no = t2.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t3 ON
t2.comp_item_no = t3.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t4 ON
t3.comp_item_no = t4.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t5 ON
t4.comp_item_no = t5.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t6 ON
t5.comp_item_no = t6.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t7 ON
t6.comp_item_no = t7.item_no LEFT OUTER JOIN
(SELECT item_no, comp_item_no, qty_per_par,
scrap_factor
FROM BMPRDSTR_SQL) t8 ON
t7.comp_item_no = t8.item_no
WHERE (b.item_no IN (@.gross_item_input)) AND t4.item_no LIKE '[019]%') e
WHERE e.comp_item_no IS NOT NULL AND e.comp_item_no LIKE '[019]%' AND
e.item_no IN (@.gross_item_input)
ORDER BY 4, comp_item_no
GO
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1140021256.388828.38310@.g43g2000cwa.googlegroups.com...
> Mark wrote:
> We don't know what sr_GrossRequirements does so we can't answer that
> for sure. My intuition says that the answer will be a big YES. If you
> didn't already know the answer to that question then you probably
> shouldn't have embarked on writing the cursor to start with.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thank you Payson. That explains it perfectly.
Mark
"Payson" <payson_b@.hotmail.com> wrote in message
news:1140022691.696195.305020@.z14g2000cwz.googlegroups.com...
>I can make a few general comments on this.
> loop while < true condition>
> read
> if eof
> break out of loop
> end if
> process
> end of loop
> The advantage to this one that it is simpler to read and maintain. The
> divantage is that you have two condition checks instead of one each
> cycle. I am not sure this is a significant now as it was 20 years ago.
> read -- priming read
> loop until eof
> process
> read -- trailing read
> end of loop
> This is almost a simple for me to read, but then I am used to it.
> There is only one conditional check per cycle.
> Control break logic, especially at eof, is more complicated in the
> second format than the first.
> Hope this makes some sense.
> Payson
>
> Mark wrote:
>