cursor vs. select

buddies,
situation: a processing must take place on every row of a table, and
output results to another table, that can't be done via an insert
into..select query (let's assume that it's not possible for now).
There're 2 solutions I have in mind:
1) open a cursor and cycle through each row (The table can have up to
1M rows)
2) create a clustered index (i.e on an identity column) then have a
loop like:
declare @.i int, @.rows int,
@.col1 varchar(20), @.col2 varchar(20),... @.coln varchar(20),
@.outval1 varchar(20),... -- output values
select @.i=1, @.rows = max(xid) from tblname -- xid is clustered indexed
while (@.i<=@.rows)
begin
select @.col1 = col1, @.col2 = col2,...@.coln = coln
from tblname
where xid = i
-- do the processing on the variables
-- then insert results to another table
set @.i = @.i+1
end
I'd like to know your ideas of which one would be more efficient. Any
other solutions are much appreciated
thanks,
TamyYou obviously skipped the essential question: "Is there a better way
than processing the data a row at a time?". 99.99% of the time the
answer is YES.

On those other occassions, it may depend on what you are doing with the
data but there probably isn't much to choose between the two
approaches. Lots of times a cursor IS the best way to process
row-by-row. For 1 million rows, though, I doubt it's even worth
considering doing it row-by-row. What makes you think that's the only
way?

> Any other solutions are much appreciated

For that we'll need a better spec so that we can write the INSERT
statement:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||>> processing must take place on every row of a table, and output
results to another table, that can't be done via an insert into..select
query (let's assume that it's not possible for now). <<

That is a **huge** leap of faith and goes against **decades** of SQL
programming experience. It also goes against some proofs in computer
science that a declarative language has the computational power of a
procedural language.

But assuming that the goal is to slow down and hurt the company as much
as possible while mis-using SQL, the cursor will be faster than the
proprietary, non-relational procedural approach. Look at the number of
passes over the base tables and time wasted on indexing.

On the other hand, if you would like to actually post the problem to
get a solution which will run order of magnitude faster instead of
asking for kludges, then you can be better helped here. I feel I like
I just got an email asking for help committing suicide.|||Haha,
chill out guys, I'm on your side. The thing is I don't have access to
the code that runs on every row (decrypting it is another story - I'd
be fired then). The no. 2) runs in 30' for 1M rows - it seems I gotta
be cool with it.|||On 8 Apr 2005 08:58:49 -0700, vuht2000 wrote:

>Haha,
>chill out guys, I'm on your side. The thing is I don't have access to
>the code that runs on every row (decrypting it is another story - I'd
>be fired then). The no. 2) runs in 30' for 1M rows - it seems I gotta
>be cool with it.

Hi Tamy,

I guess that being stuck with a stored proc that works only row-based
and that you can't legally change or replace counts as a valid reason
for using cursor-based logic.

Your solution #2 has one flaw: it assumes that all identity values will
be a contiguous range. That can't be guaranteed, though - there might be
gaps. You'll have to adapt the code to handle those.

I expect that using a cursor will be faster - IF you use the correct
settings (FAST_FORWARD comes to mind), make sure that tempdb is on a
fast disk, and have your source table properly indexed. But the only way
to know for sure which method is the fastest is to test them both, in
your environment and with your data. Comment the call to the stored proc
and the insert statement to test just the speed of then row-by-row
processing.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Cursor Process Coding Problem

Hi guys,

It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop

But aren't you worried about datatypes...and what's wrong with nulls annyway

(Here we go again)|||Ok,

I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

Here is the updated code:

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO

SELECT * FROM myTable99
GO

DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname

SET @.TABLE_NAME = 'myTable99'

SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME

SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist

SELECT @.sql

EXEC(@.sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||OK,

I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

Dave|||We resolved it in-house. Here is the answer! Thanks again.

================================================== =====

DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech

Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

Davesql

Cursor from stored procedure?

I have a situation where I need to create a cursor based on a stored
procedure, so basically something like this...
DECLARE @.proc varchar(250)
Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
Cast(@.LastUpdateDate As Varchar(7))
DECLARE the_cursor CURSOR FOR @.proc
SQL Server expects a 'Select' statement instead of an SP name and therefore
is giving me an error. I am building the stored procedure name to include
the server name because I am linking with the @.DBServerName server before
this statement (with sp_addlinkedserver).
I tried just issuing a built 'Select' statement against the linked server,
but again, the 'Select' statement had to be built as a string to include the
linked server name. Is there a Transact-SQL version of eval() or something
like that to tell it to treat the string version of the 'Select' statement a
s
if it were a 'Select' statement that was just typed in (not built as a
string)? Or, is there a way to build a cursor by defining it to be a stored
procedure instead of a 'Select' statement? Seems as though I'm in a catch-2
2
unless I'm missing something.
Thanks,
ToddI dont think you can define based directly on a SP.
Instead, store the results in a Temp table and base your cursor on the temp
table.
Gopi
"Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>I have a situation where I need to create a cursor based on a stored
> procedure, so basically something like this...
> DECLARE @.proc varchar(250)
> Set @.proc = '[' + @.DBServerName + '].thedb.dbo.spGetBCXDiags ' +
> Cast(@.LastUpdateDate As Varchar(7))
> DECLARE the_cursor CURSOR FOR @.proc
> SQL Server expects a 'Select' statement instead of an SP name and
> therefore
> is giving me an error. I am building the stored procedure name to include
> the server name because I am linking with the @.DBServerName server before
> this statement (with sp_addlinkedserver).
> I tried just issuing a built 'Select' statement against the linked server,
> but again, the 'Select' statement had to be built as a string to include
> the
> linked server name. Is there a Transact-SQL version of eval() or
> something
> like that to tell it to treat the string version of the 'Select' statement
> as
> if it were a 'Select' statement that was just typed in (not built as a
> string)? Or, is there a way to build a cursor by defining it to be a
> stored
> procedure instead of a 'Select' statement? Seems as though I'm in a
> catch-22
> unless I'm missing something.
> Thanks,
> Todd|||The problem is that I have to build a string that represents the remote
stored procedure or string that represents a 'Select' statement. I must
build the string representations in order to dynamically include the remotel
y
linked server name into the equation. If I build a string representation of
an SP SQL Server will run it, but I have no way of seeing the records it
returns that I know of (can't build a cursor from an SP). If I build a
string representation of a 'Select' statement that includes the remote serve
r
name, how the heck do you tell SQL Server to run the 'Select'? It's just a
string value to SQL Server.
I guess my other options are to have a separate SP for each server I'm going
to link to or to put the code on each SQL Server machine and link back up
with the main server. That way the linked server name will be constant. I
didn't want to do either of these unless, of course, the other way just won'
t
work.
"rgn" wrote:

> I dont think you can define based directly on a SP.
> Instead, store the results in a Temp table and base your cursor on the tem
p
> table.
> Gopi
> "Todd Bright" <ToddBright@.discussions.microsoft.com> wrote in message
> news:AB4C0DE3-DC07-4BFB-99AB-EB10320B37FF@.microsoft.com...
>
>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:
>