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:
>

Cumulative update packages for Post-SP2 changes in SQL2005

Hi !
I see there are quite a few cumulated post-SP2 packages that have been
built, some of which fix errors that I have been experiencing in my
environment.
I have doubts if that's good practice to apply those cumulative packages to
my life production servers... is it ?
Any idea on how long we have to wait until SP3 will be built ?
PalliHi
I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
you to contact PSS for them. In general hotfixes are cumulative so you only
need to apply the last one.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:etaKos7cIHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hi !
> I see there are quite a few cumulated post-SP2 packages that have been
> built, some of which fix errors that I have been experiencing in my
> environment.
> I have doubts if that's good practice to apply those cumulative packages
> to my life production servers... is it ?
> Any idea on how long we have to wait until SP3 will be built ?
> Palli
>|||>
> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
> you to contact PSS for them. In general hotfixes are cumulative so you
> only need to apply the last one.
>
I did contact MS about the cumulative packages, and I did receive one from
them for 1 or 2 weeks ago, I think it's number 5.
1) I haven't had the guts yet to apply them, so the question is, should I
apply them to my production servers ?
2) Will I have to uninstall them before applying SP3 ?
Palli|||Hi
You should apply any service pack or hotfixes to a test machine and make
sure they work as expected and don't break anything else, once satisfied you
can then apply them to a production system, but make sure that you have a
rollback process and recovery procedures in place to copy with any
eventuality.
You should be able to apply any new hotfix/service pack ontop of the one you
have providing the version number is higher.
John
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||Palli,
If you are registered on Connect then vote here:-
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
We are running CU2 on one production system and are looking at upgrading to
either CU5 or CU6 but only after fully testing on Development and Acceptance
servers first. On one server we get mini dumps associated with a fix
included in CU4.
Chris
"Pall Bjornsson" <palli@.kvos.is> wrote in message
news:eQEm4A8cIHA.2268@.TK2MSFTNGP02.phx.gbl...
> >
>> I can't say if/when SP3 is due, but most of the post SP2 hotfixes require
>> you to contact PSS for them. In general hotfixes are cumulative so you
>> only need to apply the last one.
> I did contact MS about the cumulative packages, and I did receive one from
> them for 1 or 2 weeks ago, I think it's number 5.
> 1) I haven't had the guts yet to apply them, so the question is, should I
> apply them to my production servers ?
> 2) Will I have to uninstall them before applying SP3 ?
> Palli
>|||On Wed, 20 Feb 2008 12:21:42 -0000, Pall Bjornsson wrote:
>Hi !
>I see there are quite a few cumulated post-SP2 packages that have been
>built, some of which fix errors that I have been experiencing in my
>environment.
>I have doubts if that's good practice to apply those cumulative packages to
>my life production servers... is it ?
>Any idea on how long we have to wait until SP3 will be built ?
Hi Palli,
Read
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/02/01/want-a-service-pack-ask-for-it.aspx
Then make yourself heard at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Cube not refreshing?

AS and RS 2005

My cube is built on views both for dimensions and the fact table.

I am using reporting services and the report i have designed works fine except it does not update when i change the data. I can see the change in a select from the view so it is definitly there. It does refresh if i reprocess the cube. This happens in rolap as well as molap.

Any Ideas?

Thanks,

Jules

Moving to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

why have you moved this?

does that mean it is reporting services that is caching the data?

It looks more like a problem with analysis services to me given that the report displays the correct data if a reprocess the cube.

Whats you thinking here.

Please help

Thanks,

Jules

|||

Most chances it is Reporting services problem. You can try and browse your AS cube using cube browser built-in in SQL Management studio to see if new numbers are avaliable.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube DDL reverse engineer ?

Hi guys

I have built several cubes. However I would need to create local cubes from the built cubes in Analysis Services. As a result I need to work out the DDL for my existing cubes. However the cube contains many mdx calculated members. I was wondering if it would be easier to simply generate the DDL from my existing cube in Analysis Services.

Does anyone know any third party tools that I can use to reverse engineer the DDL from the built cubes in Analysis Services ?

Thanks

Tom

Why not just load it in Server Manager and tell it to Script Database as Create. That should give you the full DDL I believe.|||I want to script the cube not the sql server database.

If there is a way could you please give a more detailed description of the process.

I really appreciate it ! thankyou !

Tom|||The cube is contained in an Analysis Services database, not a SQL Server database. Or are you saying that your AS database contains multiple cubes and you only want to script out one?

Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To

Alternatively if you want just one cube expadn the cubes node
Right click on the cube you are interested in
Select Script Cube as Create To|||Start SQL Server Management Studio
When asked to connect to server drop the Server type down and pick Analysis Services
Expand the databases node.
Right click on a database
Select Script Database as Create To
__

I've started sql server enterprise manager. When I tried to make a new registration under a sql server group it DOES NOT pick up analysis services. I know they're running on different ports why would sql server manager pickup analysis services ?

can you confirm the process please. I really can't see it working. As there's no possible way for sql server manager to connect to analysis services.

Thankyou
Tom

|||Tom,

I think I see the problem. I'm betting you are using Analysis Services 2000, not Analysis Services 2005...

With SQL Server 2005, the new management tool (called SQL Server Management Studio) replaces Enteprise Manager in SQL Server 2000. SQL Server Management Studio does indeed have the ability to connect to Analysis Services as well as SQL Server. And it has the ability to script the DDL for an entire Analysis Services 2005 database as well as any portion thereof.

However, since you are likely using Analysis Services 2000, this won't work for you. If you need to generate the DDL to create a cube, you'll have to look to some third-party tool options for this as Analysis Manager does not have the ability to generate DDL. Take a look at the short list of options mentioned on Mosha's website at http://www.mosha.com/msolap/util.htm#Metadata. This should at least give you some ideas and options...

Dave Fackler
|||thanks for that at least now I have a good idea what the other person's talking about

Thanks
Tom