can i transfer data from two tables to a single table at a time (row wise)

i need to transfer the data from allbase (old database) to sql server 2005 .

moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).

and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.

and please help me to create custom component if needed.

thanks in advance

B L,

I would reccomend you to go to books on line; there are some tutorials that will help you on getting started. Getting some books will help you as well. Then you can come back with specific questions/issues.

http://msdn2.microsoft.com/en-us/library/bb418492.aspx

|||

thanks for immediate response .

we have different data flow sources in toolbox but there is nothing for odbc .

If i want to connect to odbc what item should i use?

and for transferring data from source to destination which item i should use in in data flow transformation.

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

|||

Few things:

Toolbox is context sensitive; it will show a diffrent set of tools depending on where you are: control flow or data flow To pump data from an ODBC source: Create a connection manager that points to your ODBC source (DNSmust be define in the machine running the package) Create a data flow task in in the control flow In Data flow task add a data reader that uses the connection manager you created add a destination component COnnect the source component to the destination component.|||

thanks for valuable suggestion .

for transferring data from old database to new one when the structure is different means

--> the number of tables / name of tables is different.

--> the structure of table is also different say the old table contains n number of columns and the new table may contain either n+ or n- number of columns and some columns in new table might not be there in old table .can i have new columns as primary?

can u guide me in that way and can you tell me which items i should use in data flow task.

|||

hello,

i have data in old database which contains many tables and i want to retrive data from two tables and insert that data into one table in the destination by SSIS package.

for example:

input output

- --

userinfo ( table ) userdetails ( table )

user_id --> id

userdata ( table2 )

name --> name

city --> city

can i do like that? please suggest me to do so .

|||

There are several ways. My first approach would be to write a query (IF the 2 source tables are in the same DB); then use that query in the source component. That way the dataflow will have all required columns right out of the source component.

Another apporach is to use 2 source componnets and then a Merge Join transformation to join the 2 data sets. This approach requires the 2 data sets to be order before hiting the merge join. Performance can also suffer with this approach since the Join takes place inside SSIS rather than in the DB engine.

|||

B L Rao wrote:

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

The script task and script component only support VB.NET, not C#.

You can build your own custom SSIS component DLLs using any .NET language, but this adds a LOT of complexity.

|||

hi Rafel Salas ,

thank you for your reply by which i got some idea ,

yes we have those tables in same DB.

but still some confusion in that first approach that is where to write that query and if possible what query i can write .

so plz give me clear idea .

and second approach is clear to me .

thanks and regards

B L Rao.

|||Just write the query that gives the desired output and then you can place it directly in the OLE DB source component by changing the data access mode to "SQL Command". Another option is to place the query in a SSIS string variable and then use data access mode "SQL Command from variable"|||

hi,

The approaches are best suitable in the scenario where we can make join between two tables. Means when we have one common column on the basis of which we can create join.

But what should we do if we want to fetch data of column1, column2 from table 1 and data of col5 from table 2 without making join.

For example

table 1 has

column1

column2

column3

table 2

col1

col2

col3

col4

col5

I have tried Union All task, and Merge task but it returned 2 rows like following

null,null,col5

column1,column2,null

but output through SSIS in destination table 3 should be combination of three columns in one row like this :

column1,column2,col5 .

can you please help as soon as possible?

if possible can u please send a small package or query for this?

thanks & Regards,

Hariax

|||Try creating a dummy column (like a simple sequential row number) in each table to join on and use the Merge Join.|||

Hi,

Please help me, I am getting the 'DTS_E_UNABLETORETRIEVEMETADATA' error while I am performing bellow mentioned steps.

1. Created 'Execute Sql Task'

2. In the editor of this task, General Page ->Sql statement, I wrote create table query.

3. I tried to execute the package, and got following error.

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [SQL Server Destination [376]]: The metadata for "[dbo].[temptable]" cannot be retrieved. Make sure the object name is correct and the object exists.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "SQL Server Destination" (376)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

If anybody has the solution of this then please let me know.

same query is running successfully from sql server management studio.

Thanks & Regards,

Hariax J. Thoria

|||If you are creating the table for the destination in the same package, make sure the DelayValidation property on the SQL Server Destination is set to TRUE.

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>

Can I modify my input data set in using DMX in mining structure?

Hi, all here,

I have a questioin-is it possible to modify input data set in mining structure using DMX?

Also, what else SQL systax staments are supported in DMX besides SELECT statement?

Thanks a lot in advance for any help.

You can have calculated expressions in the input query for prediction and training (if you're using DMX INSERT-INTO). Also, you can modify the input data set by introducing named calculations in the data source view used by a mining structure. Is this what you're looking to do?

DMX supports a very limited form of SQL for its SELECT statement. See http://msdn2.microsoft.com/en-us/library/ms132025(SQL.90).aspx for a full reference of DMX statements. Note that DMX is designed to be SQL-like, not SQL-compliant, meaning that the semantics of statements like INSERT differ somewhat from the corresponding relational SQL operations.