install SQL on WinXP: working

Showing posts with label working. Show all posts

Sunday, March 25, 2012

Can not create Data-Driven Subscriptions

I setup a production server today I thought the same as a development server
that I was working on. However, I can not see the Data-Driven Subscriptions
button to create a new data-driven subscription. My account is a system
administrator and also a content manager. On my development server I can
create a data-driven subscription. What did I miss?did you install the Standard Edition? I don't think it supports Data
Driven Subscriptions.|||That is the version that I installed. I'll have to look for the documentation
that says I can't do that then. Thanks.
"bri" wrote:
> did you install the Standard Edition? I don't think it supports Data
> Driven Subscriptions.
>|||You need Enterprise Edition to utilize Data Driven Subscriptions
"Brian" wrote:
> That is the version that I installed. I'll have to look for the documentation
> that says I can't do that then. Thanks.
> "bri" wrote:
> > did you install the Standard Edition? I don't think it supports Data
> > Driven Subscriptions.
> >
> >

Tuesday, March 20, 2012

can not access reports

This was working before but all of a sudden when I try to access reports server
http://myserver/reports/
I get the following error:
Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\global.asax" is denied.
Please help..Looks like permissions or users have changed. The Web service user needs
access to this file. Make sure the Web service identity user has
permissions to that file.
| Thread-Topic: can not access reports
| thread-index: AcXEcWiSSksr+4EXS/e+bsnVf1Lr4w==| X-WBNR-Posting-Host: 216.89.4.2
| From: "=?Utf-8?B?c3Fsc3Rlcg==?=" <nospam@.nospam.com>
| Subject: can not access reports
| Date: Wed, 28 Sep 2005 14:13:01 -0700
| Lines: 12
| Message-ID: <CED59F58-B2D0-4768-B03B-64B9590467A1@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53316
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| This was working before but all of a sudden when I try to access reports
server
|
| http://myserver/reports/
|
| I get the following error:
|
| Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
| Services\ReportManager\global.asax" is denied.
|
| Please help..
|
|
|

Monday, March 19, 2012

can no longer connect to SQL Server express error:40

Hi,

I have been working for a few weeks on a local SQL server (express) on my computer all worked ok, until 2 days ago I got the following error message:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Where can I find the setting for remote connections? Also any idea why this happened?

rgds

Frank

Hi,

see my Screencasts on my website for enabling / configuring Remote Connections.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can multiple Reporting Services use one SQL Server?

I'm working with a team that's trying to get a new set of development servers working and have run into all sorts of issues, mostly w/ Team Foundation Server and RS, but it has caused to us to ask a number of questions about the use of RS.

All our servers are virtual servers, running W2003. We have:

+ one SQL 2005 server installed w/ SSIS, SSAS, and just the RS databases.

+ one Web server (IIS6) hosting multiple websites. I installed the RS server components on this server as well.

+ [attempting to get working] on Team Foundation Server (TFS) using the previously mentioned SQL Server as its data tier; Report Server installed on this box too, by TFS.

Getting the SQL and Web server boxes installed and running was a snap, no problems there. TFS, on the other hand, has been a nightmare. So here's my question, based on various incongruent feedback from tech support...

Can there be multiple applications (like mulitple different web servers (not a web farm, but say 2 distinct and different web server boxes, and something like a TFS server) all use one SQL Server's Reporting Server databases on the backend?

It seems as if we're being told that either it can't be done or perhaps we need to use SQL Server Enterprise edition... which would blow out our expenses considerably.

Can anyone shed light on this? Thanks in advance.

If you have 2 RS instances (web servers) connecting to the same RS database, it is a web farm scenario and requires enterprise edition.

On the other hand, you can have 2 RS instances (web servers) each connecting to its own database. If the RS instance and its database is on different servers, you will need enterprise or standard edition. If they are on the same server, you can use any edition.

|||

"each connecting to its own database"... OK, I might need some more help on that. So, I run SQL Server setup on WebServer1. OK, that's fine so far. Does that create the ReportServer and ReportServerTempDB databases on my single SQL Server box?

Now, I run the SQL Server setup disc on WebServer2, do I need to tell it to use different database names at some point?

Sorry for the confusion, just trying to get clarity around all this. I have two separate web servers and one sql server, 3 all told (actually 1 physical box, 3 virtual servers, but that shouldn't matter should it?). WebServer1 and WebServer2 are running different websites altogether. I want each web server to be able to use Reporting Services technology as needed and I want to only have one Sql Server box, preferably Std Edition. I guess that's what I'm trying to find out if it's feasible.

Thanks again.

|||

Virtual server makes no different in the argument.

Do you need the 2 web sites to share content? (i.e. you upload a report to website1, and it shows up on website2) If so the 2 websites need to point to the same RS databases on your SQL machine, and it's a web farm scenario.

If the websites can be independent, then you can use standard edition. You install RS on webserver1, using the "file-only" install option. Post-install you use the configuration tool and create RS databases for webserver1. You do the same for webserver2, but the database will be different. There will be a total of 4 databases on the SQL machine, 2 for webserver1, and 2 for webserver2.

Saturday, February 25, 2012

Can I Use If Condition In View

HiiiALL..
I AM CREATING A VIEW AND USE IN THIS IF AND ELSE CONDITION.
THIS SYNTAX IS NOT WORKING PROPERLY..CAN USE OR NOT??No, you can not use IF condition in a view.
There are so many other ways to do it.

Thank you.|||

Quote:

Originally Posted by iburyak

No, you can not use IF condition in a view.
There are so many other ways to do it.

Thank you.

thank u sir for reply
u say that there is many ways.
so sir what is the other way..|||1. You can create a table function.
2. You can return result of a stored procedure.
3. You can use where condition on existing view.
4. You can use Case in select statements of a view which is in many cases is almost the same as IF ELSE.
5. You can create different views and use them depend on a conditions like.
Create view View_StoredProcs
AS
select name from sysobjects where type like 'p'

Create view View_Tables
AS
select name from sysobjects where type like 'u'

and more....|||Hi iburyak,

I've used the case statement and it is working as expected here.

Thanks for the excellent explanation.|||

Quote:

Originally Posted by leniel

Hi iburyak,

I've used the case statement and it is working as expected here.

Thanks for the excellent explanation.

You are welcome.
For generic question I had a generic answer.
I am glad you were able to get some info out of it... :)
Good Luck.

Can I use business objects as a data source?

We have decided to use business objects in our new application, which seems to be working well, since they can be used as a binding source for grids, etc.

I am trying to evaluate whether SSIS is a practical solution for our ETL requirements. The problem is, I can't find any examples or references, or even the slightest hint that anyone is using them with business objects. Any attempts to search yield a ton of results which are based on a commercial product called "Business Objects" rather than the design pattern.

It is currently a requirement of our development team that all data access must be done via business objects, rather than communicating directly with the database.

Can anyone provide some more information (besides just suggesting I write a custom connection manager)? Is there anyone who has actually made SSIS work with business objects?

Thanks, Richard

What is a "business object"?|||I don't know what kind of information you're looking for. Yes, it can be done. Yes, you'll have to write custom components to talk to the business objects instead of the database. I've done it for Reporting Services as a custom data extension (source) and it wasn't a bunch of fun. SSIS I think would be even less. Biggest downside I can think of off-hand is that you won't be able to bulk load into the database (think row-by-row), which is going to be really painful in an ETL scenario. I've never heard of an ETL process that wasn't allowed to access the database directly.
|||I'd concur with Jay's assessment. I've done some similar work in the past. Business objects are usually not very efficient for accessing large amounts of data. If your data movement is going to be on an individual row basis, I'm not sure SSIS is the best option for implementing it.|||

We are a 3PL, so in our case, ETL is more along the lines of importing batches of orders and exporting batches of shipping confirmations from and to a variety of data formats. Row-by-row is not an issue.

It would be nice to find a working example of how to do this prior to investing a bunch of time into SSIS.

|||This sounds more like BizTalk than SSIS. It isn't your business objects that I'd be concerned about in SSIS, it's the "variety of data formats". SSIS wants to move 10,000+ rows per second between well-defined sources and destinations and do a bunch of transformations along the way. BizTalk wants to route transactions between different systems and talk different formats to each one.
|||

BizTalk has a price tag. We already have SSIS. Everything I've read to date on it indicates that it is something we should be able to use to meet our needs. I am not questioning whether SSIS is the best choice. I would like to know if anyone has used it successfully with business objects, and if so, can they provide some examples, or other helpful information.

Thanks, Richard.

|||What will your business objects be constructed as? COM objects, web services, .NET assemblies? That has an impact on how easy they are to interface with.|||

SSIS Evaluator wrote:

BizTalk has a price tag. We already have SSIS. Everything I've read to date on it indicates that it is something we should be able to use to meet our needs. I am not questioning whether SSIS is the best choice. I would like to know if anyone has used it successfully with business objects, and if so, can they provide some examples, or other helpful information.

Thanks, Richard.

Can I reiterate Phil's question (above)? What exactly are these "business objects"?

-Jamie

|||

It's a generic term for objects that represent business entities and the rules associated with those entities. So you might have a Customer business object, with all the attributes that a customer has, and methods to represent any operations that can be performed on the customer. Usually, these provide a layer of abstraction between the database and the user interface. Business objects know where their data is stored for persistence, and user interfaces request information from the business objects rather than accessing the database directly. Changes in the data from the UI are also handled through the business object. Data validation, enforcement of business rules, etc, are all handled in the business object.

In the OP's case, evidently they have made an architectural decision that all data access has to go through these business objects. As has been stated, it is possible to do this with SSIS. The degree of complexity depends in large part on how the business objects are implemented. If this is being looked at a method to handle importing or exporting data from business objects to an external database, it might work OK, assuming performance is not the major concern. If it is for business object to business object communication, I really think the OP would be better off investigating other tools.

Can I use AMO from .net 2003?

I'm working with SQL server 2005 beta 2 from C# 2003
I want to know if I can somehow work with amo ?

I would really appreciate any kind of sugestionAMO (Microsoft.AnalysisServices.dll) is built and works with .net framework 2.0, so you will not be able to use it with Visual Studio 2003 (that uses .net framework 1.1).

SQL Setup 2005 install .net framework 2.0, so you can build AMO applications from command line with csc.exe. Or use Visual Studio 2005 Beta 2 (http://lab.msdn.microsoft.com/vs2005/).

Adrian.

Friday, February 24, 2012

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

-ZZ

|||

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

Can I take print the report using crystal report.

Hi,
I am displaying the report through web using jsp and IBM RAD
Everything is working fine . but I am able to take the print through browser.

Now I am unable to take print of a report using crystal report. .

Is it possible to print the report ? .

when I click on the print option on a tool bar of a crystal report 10.
it display the print dialog box.
but it will not print the report .
But the report will be displayed in another browser.
Please send me the solution as soon as possible it is very very urgent...
Regards
Naltu.Make sure Printer is properly connect to the system

Sunday, February 19, 2012

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 set font/color attributes to reports from a template like for eg. css ?

I have 20 rdl reports that I am working on. I will have to keep a
consistency at the colours, backgroud colours, fonts etc in all of the
reports. Is there any one place where I can define all these
attributes and somehow apply the same attributes to all the reports
with a single stroke(either at the designer or through a .net program)
I mean like calling the css style from a html page.
Consider the above scenario, I guess there are 2 ways i can choose to
build the reporting system. 1) Build all the reports and give the URLs
of the reports to a calling main application 2) Create a .Net program
which calls each report by the programatic rs.render(,,,..) method.
whichever way I build the system I need to have a common way of
setting the attributes. Is that possible ? Please help with solid
ideas/code
Thanks,
Anand SagarSQL Server 2000 Reporting Services has somewhat limited style template
support. Improving support in this area is being looked at for inclusion in
a future release.
Until then you have the following options:
1. You can approximate this behavior by creating the specific report you
want and then placing it in C:\ProgramFiles\Microsoft SQL
Server\80\Tools\Report Designer\ProjectItems\ReportProject directory. These
reports "templates" will show up in Report Designer's Add New Item dialog
along with the Report Wizard, Report, and Data Source templates when
you add a new report to a project.
2. For simple wizard-created reports, you can control the style via
modifying the wizard style templates (there's an XML config file for this
documented in the help)
3. If you're wanting to modify the style dynamically after the reports are
created, you could store style information in your database and then query
for that information in your report, setting style properties based on the
query results (e.g. FontFamily
=First(Fields!DetailRowFontFamily.Value,"StyleDataSet"))
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anand Sagar" <anandsagar@.gmail.com> wrote in message
news:3f76a771.0408110416.53ab9992@.posting.google.com...
> I have 20 rdl reports that I am working on. I will have to keep a
> consistency at the colours, backgroud colours, fonts etc in all of the
> reports. Is there any one place where I can define all these
> attributes and somehow apply the same attributes to all the reports
> with a single stroke(either at the designer or through a .net program)
> I mean like calling the css style from a html page.
> Consider the above scenario, I guess there are 2 ways i can choose to
> build the reporting system. 1) Build all the reports and give the URLs
> of the reports to a calling main application 2) Create a .Net program
> which calls each report by the programatic rs.render(,,,..) method.
> whichever way I build the system I need to have a common way of
> setting the attributes. Is that possible ? Please help with solid
> ideas/code
> Thanks,
> Anand Sagar

Sunday, March 25, 2012

Can not create Data-Driven Subscriptions

Tuesday, March 20, 2012

can not access reports

Monday, March 19, 2012

can no longer connect to SQL Server express error:40

Can multiple Reporting Services use one SQL Server?

Saturday, February 25, 2012

Can I Use If Condition In View

Can I use business objects as a data source?

Can I use AMO from .net 2003?

Friday, February 24, 2012

can I use * to specify 'Output Column' for OLD DB Source Editor?

can I use * to specify 'Output Column' for OLD DB Source Editor?

Can I take print the report using crystal report.

Sunday, February 19, 2012

Can I Simplify and Optimize This Long Query?

Can I set font/color attributes to reports from a template like for eg. css ?

install SQL on WinXP

Blog Archive

About Me