Can I Simplify and Optimize This Long Query?

Thanks in Advance.
I am working with a single table that has the structure as the first item
below. This table is a bill of materials structure for discrete
manufacturing. The parent and child item relationship in one row is
frequently follow a child and grand child relationship as you can see in the
second item below. This table can go six layers deep in the bill of
material for some of the items that we make.
The third item down below is a query that I wrote which accurately recreates
a report in our application (I am not privy to the actual code that it is
written in). If I pass it the number of a manufactured product and a qty to
be made, it always provides me the exact number of components required to
make that qty of parent products.
I believe that the code can be optimized futher, probably in the join
method. Possibly some other way. Currently it takes 5.5 seconds to render
one parent item with a quantity. That is almost 37 minutes for 400 items.
The index tuning wizard can do nothing further for me. How can I write this
better?
Mark Simmerman
Napa, California, USA
SQL Learner
First Item:
[parent] [char] (15) ,
[sequence_no] [smallint] NOT NULL ,
[child] [char] (15) ,
[alt_parent] [char] (15) ,
[qty_per_parent] [decimal](12, 6) NULL ,
[scrap_factor] [decimal](4, 1) NULL ,
[activity_flag] [char] (1) ,
[effectivity_date] [int] NULL ,
[obsolete_date] [int] NULL ,
[scrap_quantity] [decimal](13, 4) NULL ,
[id] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
Second Item:
parent sequence_no child alt_parent qty_per_parent scrap_factor
activity_flag effectivity_date obsolete_date scrap_quantity id
001 10 FOR1 001 12.375 0 A 0 0 0 9327
001 20 SUBPKG1 001 1 0 A 0 0 0 8090
001 30 9001 001 0.012 1.5 A 20010517 0 0 46
001 40 5002 001 0.096308 0 A 20010727 0 0 47
001 50 5001 001 0.2883 0 A 20010727 0 0 48
001 60 5003 001 1.317801 0 A 20010727 0 0 49
001 70 5004 001 0 0 A 20030501 0 0 5651
FOR1 10 1630 FOR1 0.44 0 A 20010517 0 0 9333
FOR1 20 SUBFOR1 FOR1 0.56 0 A 20010517 0 0 9334
SUBFOR1 10 4900 SUBFOR1 0.746557 0 A 20010517 0 0 2825
SUBFOR1 20 4870 SUBFOR1 0.28288 0 A 20010517 0 0 2826
SUBFOR1 30 4103 SUBFOR1 0.000045 0 A 20010517 0 0 2827
SUBPKG1 10 7050 SUBPKG1 12 2 A 20010517 0 0 7730
SUBPKG1 20 7455 SUBPKG1 12 1.5 A 20010517 0 0 7731
SUBPKG1 30 8108 SUBPKG1 1 0 A 20010517 0 0 7732
SUBPKG1 40 8149 SUBPKG1 0.034 0 A 20010517 0 0 7733
SUBPKG1 50 8900 SUBPKG1 0.0079 0 A 0 0 0 7734
Third Item:
--LAYER FIVE ITEMS IN THIS TABLE
SELECT layer5_parent AS 'child',
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.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty',
t3.child AS 'layer3_parent', t3.qty_per_parent +
(t3.qty_per_parent * t3.scrap_factor /100) AS 'layer3_qty',
t4.child AS 'layer4_parent', t4.qty_per_parent +
(t4.qty_per_parent * t4.scrap_factor /100) AS 'layer4_qty',
t5.child AS 'layer5_parent', t5.qty_per_parent +
(t5.qty_per_parent * t5.scrap_factor /100) AS 'layer5_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer3_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer4_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer5_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer3_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer4_qty',
t7.child, t7.qty_per_parent + (t7.qty_per_parent *
t7.scrap_factor /100) AS 'layer5_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer3_qty',
t7.child, t7.qty_per_parent + (t7.qty_per_parent *
t7.scrap_factor /100) AS 'layer4_qty',
t8.child, t8.qty_per_parent + (t8.qty_per_parent *
t8.scrap_factor /100) AS 'layer5_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer2_qty',
t7.child, t7.qty_per_parent + (t7.qty_per_parent *
t7.scrap_factor /100) AS 'layer3_qty',
t8.child, t8.qty_per_parent + (t8.qty_per_parent *
t8.scrap_factor /100) AS 'layer4_qty','0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e5
WHERE e5.layer5_parent IS NOT NULL AND e5.layer5_parent LIKE '[019]%' AND
e5.parent IN (@.gross_item_input)
UNION
--LAYER FOUR ITEMS IN THIS TABLE
SELECT layer4_parent AS 'child',
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.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty',
t3.child AS 'layer3_parent', t3.qty_per_parent +
(t3.qty_per_parent * t3.scrap_factor /100) AS 'layer3_qty',
t4.child AS 'layer4_parent', t4.qty_per_parent +
(t4.qty_per_parent * t4.scrap_factor /100) AS 'layer4_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer3_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer4_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer3_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer4_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer3_qty',
t7.child, t7.qty_per_parent + (t7.qty_per_parent *
t7.scrap_factor /100) AS 'layer4_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer2_qty',
t7.child, t7.qty_per_parent + (t7.qty_per_parent *
t7.scrap_factor /100) AS 'layer3_qty','0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e4
WHERE e4.layer4_parent IS NOT NULL AND e4.layer4_parent LIKE '[019]%' AND
e4.parent IN (@.gross_item_input)
UNION
--LAYER THREE ITEMS IN THIS TABLE
SELECT layer3_parent AS 'child', 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.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty',
t3.child AS 'layer3_parent', t3.qty_per_parent +
(t3.qty_per_parent * t3.scrap_factor /100) AS 'layer3_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer3_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer3_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer3_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty',
t6.child, t6.qty_per_parent + (t6.qty_per_parent *
t6.scrap_factor /100) AS 'layer2_qty','0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e3
WHERE e3.layer3_parent IS NOT NULL AND e3.layer3_parent LIKE '[019]%' AND
e3.parent IN (@.gross_item_input)
UNION
--LAYER TWO ITEMS IN THIS TABLE
SELECT layer2_parent AS 'child', layer2_qty * layer1_qty * qty AS
'qty_per_parent',
layer2_qty * layer1_qty * qty * @.gross_qty_input AS 'Total',
'Layer2'
FROM (SELECT b.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty', '0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e2
WHERE e2.layer2_parent IS NOT NULL AND e2.layer2_parent LIKE '[019]%' AND
e2.parent IN (@.gross_item_input)
UNION
--LAYER ONE ITEMS IN THIS TABLE
SELECT layer1_parent AS 'child', layer1_qty * qty AS 'qty_per_parent',
layer1_qty * qty * @.gross_qty_input AS 'Total', 'Layer1'
FROM (SELECT b.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty', '0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e1
WHERE e1.layer1_parent IS NOT NULL AND e1.layer1_parent LIKE '[019]%' AND
e1.parent IN (@.gross_item_input)
UNION
--LAYER 0 ITEMS IN THIS TABLE
SELECT child AS 'child', qty AS 'qty_per_parent', qty * @.gross_qty_input
AS 'Total', 'Layer0'
FROM (SELECT b.parent, b.child, b.qty_per_parent + (b.qty_per_parent
* b.scrap_factor /100) AS 'qty',
t1.child AS 'layer1_parent', t1.qty_per_parent +
(t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
t2.child AS 'layer2_parent', t2.qty_per_parent +
(t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
UNION
SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
t1.scrap_factor /100) AS 'qty',
t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'layer1_qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
UNION
SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
t2.scrap_factor /100) AS 'qty',
t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor/100) AS 'layer1_qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'
UNION
SELECT t3.parent, t3.child, t3.qty_per_parent + (t3.qty_per_parent *
t3.scrap_factor /100) AS 'qty',
t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor/100) AS 'layer1_qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer2_qty'
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t3.parent LIKE '[019]%'
UNION
SELECT t4.parent, t4.child, t4.qty_per_parent + (t4.qty_per_parent *
t4.scrap_factor /100) AS 'qty',
t5.child, t5.qty_per_parent + (t5.qty_per_parent *
t5.scrap_factor /100) AS 'layer1_qty', '0', 0
FROM TABLE b LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t1 ON b.child = t1.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t2 ON t1.child = t2.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t3 ON t2.child = t3.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t4 ON t3.child = t4.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t5 ON t4.child = t5.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t6 ON t5.child = t6.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t7 ON t6.child = t7.parent
LEFT OUTER JOIN
(SELECT parent, child, qty_per_parent,
scrap_factor
FROM TABLE) t8 ON t7.child = t8.parent
WHERE (b.parent IN (@.gross_item_input)) AND t4.parent LIKE '[019]%') e
WHERE e.child IS NOT NULL AND e.child LIKE '[019]%' AND e.parent IN
(@.gross_item_input)Hi
Just general advice (without studying this too much!)...
You may want to break this down into an easier example and then propogate
the method upwards.
As you are using UNION it will only be selection distinct values therefore
if you try UNION ALL and get duplicates, there may be too many unions.
As there are no aggregation it may be possible to remove the derived tables
and just left join.
John
"MarkS" wrote:

> Thanks in Advance.
> I am working with a single table that has the structure as the first item
> below. This table is a bill of materials structure for discrete
> manufacturing. The parent and child item relationship in one row is
> frequently follow a child and grand child relationship as you can see in t
he
> second item below. This table can go six layers deep in the bill of
> material for some of the items that we make.
> The third item down below is a query that I wrote which accurately recreat
es
> a report in our application (I am not privy to the actual code that it is
> written in). If I pass it the number of a manufactured product and a qty
to
> be made, it always provides me the exact number of components required to
> make that qty of parent products.
> I believe that the code can be optimized futher, probably in the join
> method. Possibly some other way. Currently it takes 5.5 seconds to rende
r
> one parent item with a quantity. That is almost 37 minutes for 400 items.
> The index tuning wizard can do nothing further for me. How can I write th
is
> better?
> --
> Mark Simmerman
> Napa, California, USA
> SQL Learner
> First Item:
> [parent] [char] (15) ,
> [sequence_no] [smallint] NOT NULL ,
> [child] [char] (15) ,
> [alt_parent] [char] (15) ,
> [qty_per_parent] [decimal](12, 6) NULL ,
> [scrap_factor] [decimal](4, 1) NULL ,
> [activity_flag] [char] (1) ,
> [effectivity_date] [int] NULL ,
> [obsolete_date] [int] NULL ,
> [scrap_quantity] [decimal](13, 4) NULL ,
> [id] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
>
> Second Item:
> parent sequence_no child alt_parent qty_per_parent scrap_factor
> activity_flag effectivity_date obsolete_date scrap_quantity id
> 001 10 FOR1 001 12.375 0 A 0 0 0 9327
> 001 20 SUBPKG1 001 1 0 A 0 0 0 8090
> 001 30 9001 001 0.012 1.5 A 20010517 0 0 46
> 001 40 5002 001 0.096308 0 A 20010727 0 0 47
> 001 50 5001 001 0.2883 0 A 20010727 0 0 48
> 001 60 5003 001 1.317801 0 A 20010727 0 0 49
> 001 70 5004 001 0 0 A 20030501 0 0 5651
> FOR1 10 1630 FOR1 0.44 0 A 20010517 0 0 9333
> FOR1 20 SUBFOR1 FOR1 0.56 0 A 20010517 0 0 9334
> SUBFOR1 10 4900 SUBFOR1 0.746557 0 A 20010517 0 0 2825
> SUBFOR1 20 4870 SUBFOR1 0.28288 0 A 20010517 0 0 2826
> SUBFOR1 30 4103 SUBFOR1 0.000045 0 A 20010517 0 0 2827
> SUBPKG1 10 7050 SUBPKG1 12 2 A 20010517 0 0 7730
> SUBPKG1 20 7455 SUBPKG1 12 1.5 A 20010517 0 0 7731
> SUBPKG1 30 8108 SUBPKG1 1 0 A 20010517 0 0 7732
> SUBPKG1 40 8149 SUBPKG1 0.034 0 A 20010517 0 0 7733
> SUBPKG1 50 8900 SUBPKG1 0.0079 0 A 0 0 0 7734
>
>
> Third Item:
> --LAYER FIVE ITEMS IN THIS TABLE
>
> SELECT layer5_parent AS 'child',
> 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.parent, b.child, b.qty_per_parent + (b.qty_per_pare
nt
> * b.scrap_factor /100) AS 'qty',
> t1.child AS 'layer1_parent', t1.qty_per_parent +
> (t1.qty_per_parent * t1.scrap_factor /100) AS 'layer1_qty',
> t2.child AS 'layer2_parent', t2.qty_per_parent +
> (t2.qty_per_parent * t2.scrap_factor /100) AS 'layer2_qty',
> t3.child AS 'layer3_parent', t3.qty_per_parent +
> (t3.qty_per_parent * t3.scrap_factor /100) AS 'layer3_qty',
> t4.child AS 'layer4_parent', t4.qty_per_parent +
> (t4.qty_per_parent * t4.scrap_factor /100) AS 'layer4_qty',
> t5.child AS 'layer5_parent', t5.qty_per_parent +
> (t5.qty_per_parent * t5.scrap_factor /100) AS 'layer5_qty'
> FROM TABLE b LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t1 ON b.child = t1.parent
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t2 ON t1.child = t2.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t3 ON t2.child = t3.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t4 ON t3.child = t4.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t5 ON t4.child = t5.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t6 ON t5.child = t6.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t7 ON t6.child = t7.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t8 ON t7.child = t8.paren
t
> WHERE (b.parent IN (@.gross_item_input)) AND b.parent LIKE '[019]%'
> UNION
> SELECT t1.parent, t1.child, t1.qty_per_parent + (t1.qty_per_parent *
> t1.scrap_factor /100) AS 'qty',
> t2.child, t2.qty_per_parent + (t2.qty_per_parent *
> t2.scrap_factor /100) AS 'layer1_qty',
> t3.child, t3.qty_per_parent + (t3.qty_per_parent *
> t3.scrap_factor /100) AS 'layer2_qty',
> t4.child, t4.qty_per_parent + (t4.qty_per_parent *
> t4.scrap_factor /100) AS 'layer3_qty',
> t5.child, t5.qty_per_parent + (t5.qty_per_parent *
> t5.scrap_factor /100) AS 'layer4_qty',
> t6.child, t6.qty_per_parent + (t6.qty_per_parent *
> t6.scrap_factor /100) AS 'layer5_qty'
> FROM TABLE b LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t1 ON b.child = t1.parent
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t2 ON t1.child = t2.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t3 ON t2.child = t3.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t4 ON t3.child = t4.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t5 ON t4.child = t5.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t6 ON t5.child = t6.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t7 ON t6.child = t7.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t8 ON t7.child = t8.paren
t
> WHERE (b.parent IN (@.gross_item_input)) AND t1.parent LIKE '[019]%'
> UNION
> SELECT t2.parent, t2.child, t2.qty_per_parent + (t2.qty_per_parent *
> t2.scrap_factor /100) AS 'qty',
> t3.child, t3.qty_per_parent + (t3.qty_per_parent *
> t3.scrap_factor/100) AS 'layer1_qty',
> t4.child, t4.qty_per_parent + (t4.qty_per_parent *
> t4.scrap_factor /100) AS 'layer2_qty',
> t5.child, t5.qty_per_parent + (t5.qty_per_parent *
> t5.scrap_factor /100) AS 'layer3_qty',
> t6.child, t6.qty_per_parent + (t6.qty_per_parent *
> t6.scrap_factor /100) AS 'layer4_qty',
> t7.child, t7.qty_per_parent + (t7.qty_per_parent *
> t7.scrap_factor /100) AS 'layer5_qty'
> FROM TABLE b LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t1 ON b.child = t1.parent
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t2 ON t1.child = t2.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t3 ON t2.child = t3.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t4 ON t3.child = t4.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t5 ON t4.child = t5.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t6 ON t5.child = t6.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t7 ON t6.child = t7.paren
t
> LEFT OUTER JOIN
> (SELECT parent, child, qty_per_parent,
> scrap_factor
> FROM TABLE) t8 ON t7.child = t8.paren
t
> WHERE (b.parent IN (@.gross_item_input)) AND t2.parent LIKE '[019]%'|||Good god!
I wonder what the original business requirement was that has led to this...?
No, really. It would help us help you if we could see what this monster of a
query was supposed to do in the first place.
ML|||Is this the result of an object oriented model stored in a relational
database?
I would start by adding a Primary Key to the table, and then add some
indexes, because your table doesn't seem to have either (or you posted
incomplete information. if that is the case, you should post again).
Also, if you can replace the UNION with UNION ALL then that could help.
I haven't tried to figure out if that would change the result.
For readability, you should consider using views.
I am not very optimistic about the design. It doesn't seem to be
normalized properly. If you need to calculate something for 400 items,
then with a properly normalized data model it shouldn't be any problem
to do the calculation with one Select statement for all 400 items. But
then again, I don't know your requirements that well.
Maybe it is an option to create a few queries to denormalize the data
(in some temporary tables with indexes), and then do the calculation for
the 400 items based on those temp tables.
Gert-Jan
MarkS wrote:
> Thanks in Advance.
> I am working with a single table that has the structure as the first item
> below. This table is a bill of materials structure for discrete
> manufacturing. The parent and child item relationship in one row is
> frequently follow a child and grand child relationship as you can see in t
he
> second item below. This table can go six layers deep in the bill of
> material for some of the items that we make.
> The third item down below is a query that I wrote which accurately recreat
es
> a report in our application (I am not privy to the actual code that it is
> written in). If I pass it the number of a manufactured product and a qty
to
> be made, it always provides me the exact number of components required to
> make that qty of parent products.
> I believe that the code can be optimized futher, probably in the join
> method. Possibly some other way. Currently it takes 5.5 seconds to rende
r
> one parent item with a quantity. That is almost 37 minutes for 400 items.
> The index tuning wizard can do nothing further for me. How can I write th
is
> better?
> --
> Mark Simmerman
> Napa, California, USA
> SQL Learner
<snip>|||Is the purpose of this procedure to navigate the ancestor/descendant axis?
Monkeys navigate trees quite well:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Maybe this helps.
ML|||You have NULLs, flags, dates in INTEGERs and IDENTITY columns in an
RDBMS! WOW!!!
Do you have a copy of TREES & HIERARCHIES IN SQL? There are several
better ways to model a BOM. .|||John,
The second item is one of the easiest examples that I have! The formating
did not maintain the spacing however, which makes it hard to read.
Hopefully this insert here survives the cut and paste:
parent child qty_per_parent
scrap_factor
001 FOR1 12.375 0
001 SUBPKG 1 0
001 9001 0.012 1.5
001 5002 0.096308 0
001 5001 0.2883 0
001 5003 1.317801 0
001 5004 0
0
FOR1 1630 0.44 0
FOR1 SUBFOR1 0.56 0
SUBFOR1 4900 0.746557 0
SUBFOR1 4870 0.28288 0
SUBFOR1 4103 0.000045 0
SUBPKG1 7050 12 2
SUBPKG1 7455 12 1.5
SUBPKG1 8108 1 0
SUBPKG1 8149 0.034 0
SUBPKG1 8900 0.0079 0
Aggregation aside, the derived tables seem to be necessary to allow for the
"one off" effect. I have to allow for adding the mulitples of
qty_per_parent for t1.child, t2.child, etc. all into the layer1_qty and so
forth. If I can achieve that with a different table structure, then I would
love to see it.
Mark Simmerman
Napa, California, USA
SQL Learner
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D4B51C24-CD1E-40E4-A3AF-A754B6242C88@.microsoft.com...
> Hi
> Just general advice (without studying this too much!)...
> You may want to break this down into an easier example and then propogate
> the method upwards.
> As you are using UNION it will only be selection distinct values therefore
> if you try UNION ALL and get duplicates, there may be too many unions.
> As there are no aggregation it may be possible to remove the derived
tables
> and just left join.
> John
> "MarkS" wrote:
>
item
the
recreates
is
qty to
to
render
items.
this
scrap_factor
layer1_qty *
layer1_qty *
(b.qty_per_parent
t1.parent
t2.parent
t3.parent
t4.parent
t5.parent
t6.parent
t7.parent
t8.parent
t1.parent
t2.parent
t3.parent
t4.parent
t5.parent
t6.parent
t7.parent
t8.parent
t1.parent
t2.parent
t3.parent
t4.parent
t5.parent
t6.parent
t7.parent
t8.parent|||I will check it out. Thanks ML.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:675C7677-6584-4B50-BB6A-5F8626037DED@.microsoft.com...
> Is the purpose of this procedure to navigate the ancestor/descendant axis?
> Monkeys navigate trees quite well:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Maybe this helps.
>
> ML|||ML,
Let's say that I have 17 finished products with bills of material (BOM) that
all require the same component. How much of the component will I need if I
plan to make thousands of each of those 17 products? If I have a different
qty of that componenet, then I could simply multiply out each component qty
on the BOM by the number of each of the finished product planned, and then
sum the result. No too hard.
Now imagine if I have 400 finished products averaging 15 components with BOM
sometimes six layers deep. I can have the same component on the same BOM
three times at layers one, three, and five. Multiplying them up the tree
and coming up with a single qty for that component on the BOM requires a
good algorithm.
So this boils down to planning material purchase on the large scale. It is
frequently referred to as a gross requirements report.
Mark Simmerman
Napa, California, USA
SQL Learner
"ML" <ML@.discussions.microsoft.com> wrote in message
news:4DDE2C78-F3CE-4795-938D-6F01FC17EC49@.microsoft.com...
> Good god!
> I wonder what the original business requirement was that has led to
this...?
> No, really. It would help us help you if we could see what this monster of
a
> query was supposed to do in the first place.
>
> ML|||Gert-Jan,
Well, my hands are tied. The tables are part of an application that does
not permit me to change or delete any table or field. I cannot even rename
a field without disabling that part of the application. I can only add
additional tables to the database without breaking the application. It is
far from a normalized database (sob, sob), and I have to do my best to
overcome its deficiencies. The ID is the closest that they came to a PK,
they use unique indexes instead (I know, I know).
Since all information is in a single table, I am not sure how to proceed
with "denormalizing the data". I suppose I can create five additional
product structure tables, but then I would have to replication changes among
them which seems too much trouble.
Since each row has a child parent relationship, this has been a real
challenge for me. Thanks for your ideas.
Mark Simmerman
Napa, California, USA
SQL Learner
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42FBB7D7.50EC7E2C@.toomuchspamalready.nl...
> Is this the result of an object oriented model stored in a relational
> database?
> I would start by adding a Primary Key to the table, and then add some
> indexes, because your table doesn't seem to have either (or you posted
> incomplete information. if that is the case, you should post again).
> Also, if you can replace the UNION with UNION ALL then that could help.
> I haven't tried to figure out if that would change the result.
> For readability, you should consider using views.
> I am not very optimistic about the design. It doesn't seem to be
> normalized properly. If you need to calculate something for 400 items,
> then with a properly normalized data model it shouldn't be any problem
> to do the calculation with one Select statement for all 400 items. But
> then again, I don't know your requirements that well.
> Maybe it is an option to create a few queries to denormalize the data
> (in some temporary tables with indexes), and then do the calculation for
> the 400 items based on those temp tables.
> Gert-Jan
>
> MarkS wrote:
item
the
recreates
is
qty to
to
render
items.
this
> <snip>