Cursor loop

Hello,

I've created a stored procedure that loops through a cursor, with the
following example code:

DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)

OPEN curPeriod

WHILE @.@.FETCH_STATUS=0

BEGIN

FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod

SET @.strTekst = CONVERT(varchar, @.intPeriod)

PRINT @.strTekst

END

CLOSE curPeriod
DEALLOCATE curPeriod

The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.

Does anyone know why the loop can execute only 1 time?

Greetings,
Chris

*** Sent via Developersdex http://www.developersdex.com ***Chris Zopers wrote:

Quote:

Originally Posted by

Hello,
>
I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
>
BEGIN
>
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
>
SET @.strTekst = CONVERT(varchar, @.intPeriod)
>
PRINT @.strTekst
>
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens. It seems that
the Cursor stays at the last record or that @.@.Fetch_status isn't 0. But
I Deallocate the cursor. I have to restart the SQL Server before the
stored procedure can be used again.
>
Does anyone know why the loop can execute only 1 time?
>
Greetings,
Chris

Hi Chris,

When you say you have to restart SQL Server before it can be used
again, do you mean the server or just Query Analyser?

I suspect the issue you're having is when you next enter the stored
procedure, the FETCH_STATUS is still as it was at the end of the last
time through the loop - non-zero, and so the loop isn't executed.

I've never seen a good pattern for doing cursors that doesn't look
messy (Since most practicioners tend to try to avoid them in the first
place, no-one spends much time tidying them up).

Normal pattern for me is:

declare cursor x for select ...
declare <variables to hold the columns>

open x

fetch next from x into <list of variables>
while @.@.FETCH_STATUS = 0
begin
--Do stuff

fetch next from x into <list of variables>
end

close x
deallocate x

in short, I've never found a way to do it which doesn't have to have
the same fetch statement in two places.

Damien

PS - Usual recommendation would be to have a list of columns, rather
than select * from... However, there is disagreement over this
particular recommendation, I'd suggest you search the archives for some
lively debate on the matter.|||Chris Zopers (test123test12@.12move.nl) writes:

Quote:

Originally Posted by

I've created a stored procedure that loops through a cursor, with the
following example code:
>
DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods
DECLARE @.intYear smallint
DECLARE @.intPeriod smallint
DECLARE @.strTekst varchar(50)
>
OPEN curPeriod
>
WHILE @.@.FETCH_STATUS=0
BEGIN
FETCH NEXT FROM curPeriod INTO @.intYear, @.intPeriod
SET @.strTekst = CONVERT(varchar, @.intPeriod)
PRINT @.strTekst
END
>
CLOSE curPeriod
DEALLOCATE curPeriod
>
The problem is that this loop only executes one time, when I call the
stored procedure a second or third time, nothing happens.

This is because you check @.@.fetch_status before you fetch. This is how
you should write cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1, col2 FROM tbl

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @.par1, @.par2
IF @.@.fetch_status <0
BREAK

-- Do stuff
END

DEALLOCATE cur

Beyond the structure of the cursor loop, please notice:

1) Never use SELECT * with cursor declarations. Add a column to the
table, and your code breaks. That's bad.

2) The cursor must be declared as INSENSITIVE or STATIC (the latter
can be combined with LOCAL, the first cannot). With no specification
you get a dynamic cursor, which is rarely what you want. But dynamic
cursors can have bad impact on both performance and funcion.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cursor is the only choice?

If I want to do a process in a sequence order, is Cursor my only choice?
for example,
----
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from table
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoSomething @.ID
end
close MyCursor
deallocate MyCursor
----
In fact, I have found a way which does NOT work as followed,
select @.Null=dbo.DoSomething(id)
from table
where created>thedate
order by created
As I just metioned, it does not work. There are too many restrictions in the
function declaration. No newid(), no xxxxxx, no xxxxxx, too many
restrictions! But the code is short and clear. I like.
So I would like to know are there another choices for me in this simple
example?Hi, Frank
You can do something this:
declare @.ID int
set @.ID=(
select top 1 id from table
where created>thedate
order by created, id
)
while @.ID is not null begin
exec DoSomething(@.ID)
set @.ID=(
select top 1 id from table
where created>thedate
and id>@.ID
order by created, id
)
end
But this method would not have a better performance than a cursor. The
real solution is to rewrite the DoSomething procedure to process all
rows at once. That's what SQL is for: set-based operations. Please tell
us what DoSomething is supposed to do, or post the actual procedure,
along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
...), so we can really help.
Razvan|||Frank Lee wrote:

> If I want to do a process in a sequence order, is Cursor my only choice?
That depends what the process is. What is the thing represented by
"DoSomething" in your example code? If it is just more data
manipulation then there's a good chance that it can be done without
using a cursor.
David Portas
SQL Server MVP
--|||Go to http://www.sciencecartoonsplus.com/gallery.htm. That is what
your "exec DoSomething @.ID" is like!
I doubt you really need a cursor. In my career (`20 years of SQL
coding, 35 years total) I have written five cursors in production SQL
code; I know that if I had the CASE expression back in the old days, I
know I could have avoided three of them.
We will need more details and probably have to re-write your DDL (good
SQL programmers do not use uniqueidentifier, and names like "id" or
"@.null" even in pseudo-code).|||The Scenario for me is an (I Called) asynchornous Request-and-Process model.
--DDL
Create table Request (
ID uniqueidentifier primary key default newid(),
types int not null, --RequestType, depend on use case, not
important here.
data varchar(255) not null, --pseudo code, maybe many columns,
depend.
created datetime not null default getdate(),
done bit not null default 0
)
go
create trigger xxxxxxxxxxx on Request for insert as
begin
--Do nothing or DoSomethings which will complete in short time
--And then activate a job which process the Request
exec sp_start job xxxxxxxxxxxxxxxxxxxx
end
go
Client-Side (C/S or asp.net) insert something by using
insert into Request (types, data)
The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
or activate by Request_Insert_Trigger. There is nothing to do for the job
but just exec MyProcessSP. In MyProcessSP, there is a loop to call
DoMyProcessSP @.ID as descripted in last post.
create procedure DoMyProcessSP (@.ID uniqueidentifier) as
begin
declare @.types int
declare @.data varchar(255)
--pseudo code
select @.types=types, @.data=data
from Request
where id=@.id
if @.types=0
begin
insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else if @.type=1
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
update request
set Done=1
where id=@.id
end
go
create procedure MyProcessSP as --A caller in sequent order
begin
--pseudo code
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from request
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoMyProcessSP @.ID
end
close MyCursor
deallocate MyCursor
end
go
"Razvan Socol" <rsocol@.gmail.com>
'?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
> Hi, Frank
> You can do something this:
> declare @.ID int
> set @.ID=(
> select top 1 id from table
> where created>thedate
> order by created, id
> )
> while @.ID is not null begin
> exec DoSomething(@.ID)
> set @.ID=(
> select top 1 id from table
> where created>thedate
> and id>@.ID
> order by created, id
> )
> end
> But this method would not have a better performance than a cursor. The
> real solution is to rewrite the DoSomething procedure to process all
> rows at once. That's what SQL is for: set-based operations. Please tell
> us what DoSomething is supposed to do, or post the actual procedure,
> along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
> ...), so we can really help.
> Razvan
>|||There is a more detail example code in the post I reply to Razvan.
If you do have a good suggestion, please read that post. thx.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136020809.428204.141060@.o13g2000cwo.googlegroups.com...
> Frank Lee wrote:
>
> That depends what the process is. What is the thing represented by
> "DoSomething" in your example code? If it is just more data
> manipulation then there's a good chance that it can be done without
> using a cursor.
> --
> David Portas
> SQL Server MVP
> --
>|||Frank Lee (Reply@.to.newsgroup) writes:
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
>...
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
>...
So we still can't tell whether you can rewrite this into set-based code.
However, it cannot be denied that there is a trade-off. Even in T-SQL
it is easier to express logic scalarly, handling one row at a time. For
starters, if you want to split code between stored procedures, it's
easy to pass parameters, but you can only pass scalar parameters, not
tables. (Procedures can still share data over tables, see my article
http://www.sommarskog.se/share_data.html for some techniques.)
Rewriting existing code that uses iterative processesing into set-based
can for complex cases be quite an effort. I rewrote a central procedure
of our system in October/November, and it spent over 80 hours on that,
including testing. We have more that we need to rewrite, and the total
estimate is over 500 hours.
Obviously, there is a trade-off. As long as performance is acceptable
for the task, it can be difficult to justify a rewrite, but be prepared
that in some point in the life-time of the system, the situation may
become untenable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Frank Lee wrote:
> The Scenario for me is an (I Called) asynchornous Request-and-Process mode
l.
> --DDL
> Create table Request (
> ID uniqueidentifier primary key default newid(),
> types int not null, --RequestType, depend on use case, not
> important here.
> data varchar(255) not null, --pseudo code, maybe many columns,
> depend.
> created datetime not null default getdate(),
> done bit not null default 0
> )
> go
> create trigger xxxxxxxxxxx on Request for insert as
> begin
> --Do nothing or DoSomethings which will complete in short time
> --And then activate a job which process the Request
> exec sp_start job xxxxxxxxxxxxxxxxxxxx
> end
> go
> Client-Side (C/S or asp.net) insert something by using
> insert into Request (types, data)
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
> create procedure DoMyProcessSP (@.ID uniqueidentifier) as
> begin
> declare @.types int
> declare @.data varchar(255)
> --pseudo code
> select @.types=types, @.data=data
> from Request
> where id=@.id
> if @.types=0
> begin
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else if @.type=1
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> update request
> set Done=1
> where id=@.id
> end
> go
> create procedure MyProcessSP as --A caller in sequent order
> begin
> --pseudo code
> declare @.ID uniqueidentifier
> declare MyCursor cursor local for
> select id
> from request
> where created>thedate
> order by created
> for read only
> open MyCursor
> while (1=1)
> begin
> fetch next from MyCursor into @.ID
> if @.@.fetch_status<>0
> break
> exec DoMyProcessSP @.ID
> end
> close MyCursor
> deallocate MyCursor
> end
> go
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
Here's a slightly different approach that has worked for me in the
past. Use your scheduled job or some other scheduled prgram to pull
processes from your request table one at a time. That way you can scale
it by spawning new threads, each of which will take the next pending
request. Also it's probably easier to handle and debug errors if each
job execution is associated with only a single request at a time. You
can use TOP 1 to retrieve the next request. If your processes need to
be serialized then you can use an extra attribute to group those so
that they are taken as a sequence.
I am assuming that these requests are some unrelated and inherently
procedural tasks rather than straight data-manipulation, otherwise
there may be better solutions without cursors or procedural code.
In SQL Server 2005 we have the Service Broker architecture to take care
of messaging and queueing functionality. Take a look at Service Broker
if you haven't already.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136119382.878745.243360@.f14g2000cwb.googlegroups.com...
> Here's a slightly different approach that has worked for me in the
> past. Use your scheduled job or some other scheduled prgram to pull
> processes from your request table one at a time.
Thanks, I use SQL Agent Job.

> That way you can scale
> it by spawning new threads, each of which will take the next pending
> request. Also it's probably easier to handle and debug errors if each
> job execution is associated with only a single request at a time.
Yes, I agree.

> You
> can use TOP 1 to retrieve the next request. If your processes need to
> be serialized then you can use an extra attribute to group those so
> that they are taken as a sequence.
>
Good point. Thx.

> I am assuming that these requests are some unrelated and inherently
> procedural tasks rather than straight data-manipulation, otherwise
> there may be better solutions without cursors or procedural code.
>
Yes, they are unrelated.

>
> In SQL Server 2005 we have the Service Broker architecture to take care
> of messaging and queueing functionality. Take a look at Service Broker
> if you haven't already.
>
Yes, I know. I have test it, and try to rewrite one implementation which
used to use SQL Agent job to implement. However, I find Service Broker is
too BIG for me. I would like, and am planning, to use it to do another big
things. Thanks anyway.

> --
> David Portas
> SQL Server MVP
> --
>sql

Cursor Fetch Duplication

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

cursor and fetch statement

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fieldsSally,

I have moved your post into the forum from the MSSQL articles

MODERATOR|||

Quote:

Originally Posted by Sally1053

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fields

would you mind giving a more detailed specs? maybe FETCH is not the right solutionsql

Cursor

I have to perform a number of calculations on a table but these
calculations need intermediate values ( like for example I have to
first calculate one value before I can use it in the next step of the
calcualtion). There are two ways of doing this:
1. Have columns for intermediate values ( it is possible as my
calculations are on a temp tbl) and use update statements. One update
statement for every step in the calculation.
2. Have a cursor, loop through the resultset and do the calculations
like we would in a programming language like C#.
Which one is better? Are updates ( a number of them) faster than a
cursor?
Thanks.John
A set based solution is much faster that cursors, however since you have
not posted DDL+ sample data it is hard to suggest something
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1139492787.074798.293960@.g14g2000cwa.googlegroups.com...
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||Cursors are usually slower than an SQL operation which updates all the rows
in
one go.
I would only use a cursor for this sort of operation if there were any
locking issues.
Are Riksaasen
"John Smith" wrote:

> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||John Smith wrote:
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
Neither of the above if you can avoid them. Create derived columns in
your queries or updates rather than using temp tables. Cursors are the
method of last resort for anything in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Current Activity - Host Question

When looking at the Current Activity screen in EM, why is that the Host computer is displayed in some cases & not present in others?
For example, I have one generic report user, and in some cases I see the server name they are connecting to the sql server from and in other cases I don't. Doesn't appear to be any rhyme or reason. Is some setting not properly set?
Any direction on helping me understand this will be appreciated.
It depends on the client. Some client libraries and versions grab the name field automatically. Others wait for the application to populate it. ADO.Net does populate the machine name automatically.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23CcqYHlJFHA.656@.TK2MSFTNGP14.phx.gbl...
When looking at the Current Activity screen in EM, why is that the Host computer is displayed in some cases & not present in others?
For example, I have one generic report user, and in some cases I see the server name they are connecting to the sql server from and in other cases I don't. Doesn't appear to be any rhyme or reason. Is some setting not properly set?
Any direction on helping me understand this will be appreciated.
|||The setting is done from the client application (in the connection string). Perhaps the app doesn't
set it properly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23CcqYHlJFHA.656@.TK2MSFTNGP14.phx.gbl...
When looking at the Current Activity screen in EM, why is that the Host computer is displayed in
some cases & not present in others?
For example, I have one generic report user, and in some cases I see the server name they are
connecting to the sql server from and in other cases I don't. Doesn't appear to be any rhyme or
reason. Is some setting not properly set?
Any direction on helping me understand this will be appreciated.
|||Geoff & Tibor,
Thank you for this information, now I know in which direction to begin.
Thanx!

Current Activity - Host Question

When looking at the Current Activity screen in EM, why is that the Host comp
uter is displayed in some cases & not present in others?
For example, I have one generic report user, and in some cases I see the ser
ver name they are connecting to the sql server from and in other cases I don
't. Doesn't appear to be any rhyme or reason. Is some setting not properly
set?
Any direction on helping me understand this will be appreciated.It depends on the client. Some client libraries and versions grab the name
field automatically. Others wait for the application to populate it. ADO.N
et does populate the machine name automatically.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23CcqYHlJFHA.656@.TK2MSFTN
GP14.phx.gbl...
When looking at the Current Activity screen in EM, why is that the Host comp
uter is displayed in some cases & not present in others?
For example, I have one generic report user, and in some cases I see the ser
ver name they are connecting to the sql server from and in other cases I don
't. Doesn't appear to be any rhyme or reason. Is some setting not properly
set?
Any direction on helping me understand this will be appreciated.|||The setting is done from the client application (in the connection string).
Perhaps the app doesn't
set it properly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23CcqYHlJFHA.656@.TK2MSFTN
GP14.phx.gbl...
When looking at the Current Activity screen in EM, why is that the Host comp
uter is displayed in
some cases & not present in others?
For example, I have one generic report user, and in some cases I see the ser
ver name they are
connecting to the sql server from and in other cases I don't. Doesn't appea
r to be any rhyme or
reason. Is some setting not properly set?
Any direction on helping me understand this will be appreciated.|||Geoff & Tibor,
Thank you for this information, now I know in which direction to begin.
Thanx!

Currency symbol

Hi,
I would like to get the symbol for the currency under the environment the
report is being run... Is there a way to do it?
For example, in the US it would display a $
in UK, it would display either the Euro or the pound symbol
etc...
ThanksTypically, you would use the C formatcode for currency formatting. You could
for instance set the format property on the textbox. Alternatively, you
could use the Format(...) function call.
More information about numeric format codes is available on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"PV" <PV@.discussions.microsoft.com> wrote in message
news:BDDF44A0-3419-4D66-9151-29D48B379497@.microsoft.com...
> Hi,
> I would like to get the symbol for the currency under the environment the
> report is being run... Is there a way to do it?
> For example, in the US it would display a $
> in UK, it would display either the Euro or the pound symbol
> etc...
> Thanks