Cursor versus Temporary Table

Hi All,
I am writing a stored procedure in which I need to read some records from a table which satisfy given condition, fetch the last read record and check its value.

e.g.
SELECT *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo.

I want to read the last record of each TestReqNo and check some values from that row. For this I was thiking of reading the above Select using a cursor and then using FETCH LAST to read the last row into some variable.

But this seems to be a round about way and also i have been reading that cursor will slow the execution. IS there any other better way. Should I use temp table instead, will that be more efficient ?

Please let me know your suggestions.
Thanks,
SnigdhaYou can reverse the order and just select the first record:

SELECT TOP 1 *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo DESC|||Hi,
Thanks a lot for this solution, but there is still a little problem. The above query returns the last record of the last TestRequestNO. But I wanted to the last record of each TestRequestNO. For e.g.:

TestRequestNo TestRequestStateId
TR123 11
TR123 3
TR123 5
TR123 12

TR155 11
TR155 3
TR155 5

TR007 11
TR007 12
TR007 3

Now, out of this orderd set of TestRequestNos I want the last record of each of the TestRequestNo: TR123, TR155, TR007.

Thanks,
Snigdha

|||

So, something along these lines?

USE Northwind

SELECT t1.* FROM [order details] t1
WHERE t1.Quantity=
(SELECT MAX(Quantity) FROM [order details] t2
WHERE t1.orderid=t2.orderid)
ORDER BY t1.orderid

SELECT t1.* FROM [order details] t1 INNER JOIN
(SELECT orderid, MAX(Quantity) AS maxdate FROM [order details] GROUP BY orderid) t2
ON t1.orderid = t2.orderid
AND t1.Quantity = t2.maxdate
ORDER BY t1.orderid
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

|||Hey,
Thanks a loooot...
the first query worked perfectly the way I wanted it !!!! :)

There are minor things remaining which I think I should be able to handle.
Thanks a lot once again,
Snigdha

Cursor Update problem

I am using a cursor to take information from a temp table and either insert
or update another table. I am using a while loop to fetch all the vaules
from the cursor and then I close and deallocate the cursor. Everything runs
fine the first time but when I run the procedure again it updates the first
record with the information from the last record process from the time
before. If it helps I am running it as a job just like it will run when
completed. Ideas on why I am getting data from the previous run?Can you poste the code instead just a brief description of the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Shannon Thompson" wrote:

> I am using a cursor to take information from a temp table and either inser
t
> or update another table. I am using a while loop to fetch all the vaules
> from the cursor and then I close and deallocate the cursor. Everything ru
ns
> fine the first time but when I run the procedure again it updates the firs
t
> record with the information from the last record process from the time
> before. If it helps I am running it as a job just like it will run when
> completed. Ideas on why I am getting data from the previous run?|||Here is my code but I cannot give you the code for stored procedures call
from this code here because they are Encrypted and part of the software
program I am integrating with.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xxx]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure xxxx
AS
/*
** Declare & initialize Local Variables
***
*/
DECLARE @.iReturnCode int,
@.iRet int,
@.vchFirstName nvarchar(255) ,
@.vchLastName nvarchar(255) ,
@.vchAdSource nvarchar(255) ,
@.vchOnyxCode nvarchar(255) ,
@.vchAddress1 nvarchar(255) ,
@.vchCity nvarchar(255) ,
@.chStateCode nvarchar(50) ,
@.vchPostCode nvarchar(40) ,
@.chCountryCode nvarchar(50) ,
@.vchPhoneType nvarchar(50) ,
@.vchPhoneNumber nvarchar(40) ,
@.vchBestTime nvarchar(255),
@.vchEmail nvarchar(255),
@.iHt_Feet nvarchar(50),
@.iHt_Inches nvarchar(50),
@.iWeight nvarchar(50),
@.dtDOB nvarchar(50),
@.vchInsurance nvarchar(255),
@.vchOtherIns nvarchar(255),
@.vchInsuranceType nvarchar(255),
@.vchSem nvarchar(255),
@.vchSemSrc nvarchar(255),
@.dtTimeStamp nvarchar(255),
@.iIndividualId int ,
@.iIncidentId int,
@.onyx_cursor cursor,
@.chInsUpd nchar(1),
@.iPhoneTypeId int,
@.getDate datetime,
@.dtPreviousUpdateDate datetime,
@.iHeight int,
@.dtUpdate datetime,
@.bmi float
set @.iReturnCode = 0
set @.iRet = 0
set @.getDate = getDate()
set @.bmi = 0
set @.onyx_cursor = cursor
--local Scroll Keyset Optimistic
FOR Select
vchFirstName,
vchLastName,
vchAdSource,
vchOnyxCode,
vchAddress1,
vchCity,
chStateCode,
vchPostCode,
chCountryCode,
vchPhoneType,
vchPhoneNumber,
vchBestTime,
vchEmail,
iHt_Feet,
iHt_Inches,
iWeight,
dtDOB,
vchInsurance,
vchOtherIns,
vchInsuranceType,
vchSem,
vchSemSrc,
dtTimeStamp
from CallCenter_Temp
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
-- loop while there are still records in table
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
(convert(float,ROUND(@.iHt_Feet,0))*12))
IF @.iHeight <> 0 AND @.iWeight <> 0
BEGIN
declare @.meters float,
@.totalinches float,
@.kilos float,
@.metersq float
set @.totalinches = convert(float,@.iHeight)
set @.meters = @.totalinches/39.36
set @.kilos = convert(float,@.iWeight)/2.2
set @.metersq = @.meters * @.meters
set @.bmi = Round(@.kilos/@.metersq,0)
END
set @.vchFirstName = UPPER(@.vchFirstName)
set @.vchLastName = UPPER(@.vchLastName)
set @.vchAddress1 = UPPER(@.vchAddress1)
set @.vchCity = UPPER(@.vchCity)
set @.chStateCode = UPPER(@.chStateCode)
set @.chCountryCode = UPPER(@.chCountryCode)
-- determine phone type
SELECT @.iPhoneTypeId =
CASE LOWER(RTRIM(@.vchPhoneType))
WHEN 'home' THEN 119
WHEN 'cell' THEN 103
WHEN 'work' THEN 102
ELSE 119
END
-- if no Onyx Id is listed then search for the individual
IF @.iIndividualId is null
BEGIN
-- check to see if Person is in Onyx
exec @.iRet = wbocpscOnyxTalley
@.vchFirstName,
@.vchLastName,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
NULL
if (@.iRet <> 0)
begin
--update
set @.chInsUpd = 'U'
set @.iIndividualId = @.iRet
end
else
begin
--insert
set @.chInsUpd = 'I'
end
end
-- iIndividualId is given so this is an update
ELSE
BEGIN
set @.chInsUpd = 'U'
END
print @.chInsUpd + ' ' + @.vchLastName
if @.chInsUpd = 'I'
begin
exec @.iReturnCode = wbospsiIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
119,
null,
null,
1,
1,
0,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1,
1
if @.iReturnCode <> 0
begin
print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
ELSE
begin
select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = ospsgCheckRecordLock
@.dtUpdate OUTPUT,
@.dtPreviousUpdateDate,
1
-- if error returned then must change dtUpdateDate to current
if (@.iReturnCode <> 0)
begin
UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = wbospsuIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
null,
null,
null,
1,
1,
0,
null,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1
if @.iReturnCode <> 0
begin
print 'update failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'updated ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
end
--fetch next record
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
END
CLOSE @.onyx_cursor
DEALLOCATE @.onyx_cursor
return @.iReturnCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Can you poste the code instead just a brief description of the problem?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Shannon Thompson" wrote:
>|||Shannon,
The cursor is based on a permanent table. How are you feeding this table
before calling the sp?
AMB
"Shannon Thompson" wrote:

> Here is my code but I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[xxx]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE procedure xxxx
> AS
> /*
> ** Declare & initialize Local Variables
> ***
> */
>
> DECLARE @.iReturnCode int,
> @.iRet int,
> @.vchFirstName nvarchar(255) ,
> @.vchLastName nvarchar(255) ,
> @.vchAdSource nvarchar(255) ,
> @.vchOnyxCode nvarchar(255) ,
> @.vchAddress1 nvarchar(255) ,
> @.vchCity nvarchar(255) ,
> @.chStateCode nvarchar(50) ,
> @.vchPostCode nvarchar(40) ,
> @.chCountryCode nvarchar(50) ,
> @.vchPhoneType nvarchar(50) ,
> @.vchPhoneNumber nvarchar(40) ,
> @.vchBestTime nvarchar(255),
> @.vchEmail nvarchar(255),
> @.iHt_Feet nvarchar(50),
> @.iHt_Inches nvarchar(50),
> @.iWeight nvarchar(50),
> @.dtDOB nvarchar(50),
> @.vchInsurance nvarchar(255),
> @.vchOtherIns nvarchar(255),
> @.vchInsuranceType nvarchar(255),
> @.vchSem nvarchar(255),
> @.vchSemSrc nvarchar(255),
> @.dtTimeStamp nvarchar(255),
> @.iIndividualId int ,
> @.iIncidentId int,
> @.onyx_cursor cursor,
> @.chInsUpd nchar(1),
> @.iPhoneTypeId int,
> @.getDate datetime,
> @.dtPreviousUpdateDate datetime,
> @.iHeight int,
> @.dtUpdate datetime,
> @.bmi float
> set @.iReturnCode = 0
> set @.iRet = 0
> set @.getDate = getDate()
> set @.bmi = 0
> set @.onyx_cursor = cursor
> --local Scroll Keyset Optimistic
> FOR Select
> vchFirstName,
> vchLastName,
> vchAdSource,
> vchOnyxCode,
> vchAddress1,
> vchCity,
> chStateCode,
> vchPostCode,
> chCountryCode,
> vchPhoneType,
> vchPhoneNumber,
> vchBestTime,
> vchEmail,
> iHt_Feet,
> iHt_Inches,
> iWeight,
> dtDOB,
> vchInsurance,
> vchOtherIns,
> vchInsuranceType,
> vchSem,
> vchSemSrc,
> dtTimeStamp
> from CallCenter_Temp
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into
> @.vchFirstName,
> @.vchLastName,
> @.vchAdSource,
> @.vchOnyxCode,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> @.chCountryCode,
> @.vchPhoneType,
> @.vchPhoneNumber,
> @.vchBestTime,
> @.vchEmail,
> @.iHt_Feet,
> @.iHt_Inches,
> @.iWeight,
> @.dtDOB,
> @.vchInsurance,
> @.vchOtherIns,
> @.vchInsuranceType,
> @.vchSem,
> @.vchSemSrc,
> @.dtTimeStamp
> -- loop while there are still records in table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
> (convert(float,ROUND(@.iHt_Feet,0))*12))
> IF @.iHeight <> 0 AND @.iWeight <> 0
> BEGIN
> declare @.meters float,
> @.totalinches float,
> @.kilos float,
> @.metersq float
> set @.totalinches = convert(float,@.iHeight)
> set @.meters = @.totalinches/39.36
> set @.kilos = convert(float,@.iWeight)/2.2
> set @.metersq = @.meters * @.meters
> set @.bmi = Round(@.kilos/@.metersq,0)
> END
> set @.vchFirstName = UPPER(@.vchFirstName)
> set @.vchLastName = UPPER(@.vchLastName)
> set @.vchAddress1 = UPPER(@.vchAddress1)
> set @.vchCity = UPPER(@.vchCity)
> set @.chStateCode = UPPER(@.chStateCode)
> set @.chCountryCode = UPPER(@.chCountryCode)
> -- determine phone type
> SELECT @.iPhoneTypeId =
> CASE LOWER(RTRIM(@.vchPhoneType))
> WHEN 'home' THEN 119
> WHEN 'cell' THEN 103
> WHEN 'work' THEN 102
> ELSE 119
> END
> -- if no Onyx Id is listed then search for the individual
> IF @.iIndividualId is null
> BEGIN
> -- check to see if Person is in Onyx
> exec @.iRet = wbocpscOnyxTalley
> @.vchFirstName,
> @.vchLastName,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> NULL
> if (@.iRet <> 0)
> begin
> --update
> set @.chInsUpd = 'U'
> set @.iIndividualId = @.iRet
> end
> else
> begin
> --insert
> set @.chInsUpd = 'I'
> end
> end
> -- iIndividualId is given so this is an update
> ELSE
> BEGIN
> set @.chInsUpd = 'U'
> END
> print @.chInsUpd + ' ' + @.vchLastName
> if @.chInsUpd = 'I'
> begin
> exec @.iReturnCode = wbospsiIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> 119,
> null,
> null,
> 1,
> 1,
> 0,
> null,
> null,
> @.iHeight,
> @.iWeight,
> @.bmi,
> null,
> null,
> null,
> @.dtDOB,
> null,
> 'CCLeads',
> @.getDate,
> 0,
> 1,
> 1
> if @.iReturnCode <> 0
> begin
> print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> else
> begin
> print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> end
> ELSE
> begin
> select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = ospsgCheckRecordLock
> @.dtUpdate OUTPUT,
> @.dtPreviousUpdateDate,
> 1
> -- if error returned then must change dtUpdateDate to current
> if (@.iReturnCode <> 0)
> begin
> UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = wbospsuIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> null,
> null,
> null,|||This table (CallCenter_temp) is populated by another stored procedure that
gets a file list from a directory, puts that into a true temp table (gets
created and deleted within procedure) and batch inserts each text file:
BEGIN
/*
** Declare & initialize Local Variables
*/
DECLARE
@.iReturnCode int,
@.MyFile varchar(200),
@.SQL varchar(2000),
@.Path varchar(400),
@.onyx_cursor cursor,
@.vchFileName varchar(255),
@.vchXPCMD nvarchar(255)
select
@.iReturnCode = 0
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
'CCFiles')
BEGIN
DROP TABLE CCFiles
END
CREATE TABLE [dbo].[CCFiles] (
[vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
IF @.iReturnCode = 0
BEGIN
SET @.onyx_cursor = cursor
FOR SELECT
vchFileName
FROM CCFiles
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into @.vchFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
@.vchFileName + '"' +
' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
--SELECT @.SQL
EXECUTE (@.SQL)
IF @.iReturnCode = 0
BEGIN
set @.vchFileName = @.Path + @.vchFileName
set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
--execute master..xp_cmdshell @.vchXPCMD
END
FETCH NEXT from @.onyx_cursor into @.vchFileName
END
END
DROP TABLE CCFiles
return @.iReturnCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Shannon,
> The cursor is based on a permanent table. How are you feeding this table
> before calling the sp?
>
> AMB
> "Shannon Thompson" wrote:
>|||> I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
Personally I'd want to decrypt those procs to see if it's feasible to
rewrite your code without a cursor.
http://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||Shannon,
In the previous post you are not closing and deallocating the cursor and
this cursor.
AMB
"Shannon Thompson" wrote:
> This table (CallCenter_temp) is populated by another stored procedure that
> gets a file list from a directory, puts that into a true temp table (gets
> created and deleted within procedure) and batch inserts each text file:
> BEGIN
> /*
> ** Declare & initialize Local Variables
> */
> DECLARE
> @.iReturnCode int,
> @.MyFile varchar(200),
> @.SQL varchar(2000),
> @.Path varchar(400),
> @.onyx_cursor cursor,
> @.vchFileName varchar(255),
> @.vchXPCMD nvarchar(255)
> select
> @.iReturnCode = 0
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> 'CCFiles')
> BEGIN
> DROP TABLE CCFiles
> END
> CREATE TABLE [dbo].[CCFiles] (
> [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
> EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
> IF @.iReturnCode = 0
> BEGIN
> SET @.onyx_cursor = cursor
> FOR SELECT
> vchFileName
> FROM CCFiles
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
> @.vchFileName + '"' +
> ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
> ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
> --SELECT @.SQL
> EXECUTE (@.SQL)
> IF @.iReturnCode = 0
> BEGIN
> set @.vchFileName = @.Path + @.vchFileName
> set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
> --execute master..xp_cmdshell @.vchXPCMD
> END
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> END
> END
> DROP TABLE CCFiles
> return @.iReturnCode
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> "Alejandro Mesa" wrote:
>|||The reason I use a cursor is that having limited SQL Stored Procedure
experience (mainly VBScript SQL experience) I know of no other way to go
record by record to call these stored procedures. I have de-crypted these
procedures but by using these stored procedures instead of recreating them I
can safety insert data into the database without breaking any middle tier
rules of the software and corrupt any of the data.
The store procedures are made to put one person at a time into the database
(when a users clicks save on the web page) not bulk so this is why I am usin
g
the cursor.
"David Portas" wrote:

> Personally I'd want to decrypt those procs to see if it's feasible to
> rewrite your code without a cursor.
> http://www.planetsourcecode.com/vb/...6J00S003GU.html
> --
> David Portas
> SQL Server MVP
> --
>|||Why did you destroy Standard SQL behavior? Why are there more NULLs in
one table than should be in an entire Fortune 500 accounting package?
Do you really have a lot of data elements that are in Chinese and 255
characters long? Why do you have data type prefixes on variable names,
which is a violation of both good programming and ISO-11179? Why do you
have numeric data elements in strings? What kind of total garbage are
trying to get with things like "weight VARCHAR(50)", "@.phonetype
VARCHAR(50)", etc. And you don't seem to be aware of floating
point rounding errors (does your machine have a floating point
processor, or do you want to slow things down with a software floating
point package?)
You keep height in inches or cm then convert it for display. You do
not do this in the database. The syntax for CAST is CAST (<exp> AS
<datatype> ) -- do not use the proprietary CONVERT().
You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
But even worse, you did absolutely no design or research on the data.
Other products have a MERGE or UPSERT statement to do this. The usual
pattern in older products is:
BEGIN
-- insert the new rows
INSERT INTO Foobar
SELECT *
FROM WorkingData AS W
WHERE W.keycol
NOT IN (SELECT keycol FROM Foobar);
-- update the rows are already there
UPDATE Foobar
SET <column>
= (SELECT col FROM WorkingData AS W
WHERE W.keycol = Foobar.keycol)
WHERE keycol IN (SELECT keycol FROM WorkingData);
END;|||Since you have no idea about the situation or the database schema your
posting is just wasted useless space. Please do not bother my thread again
unless you want to know more about the database and can actually just give m
e
a reason to my error. I did not ask for a comments on the coding though
constructive criticism, not code bashing, is appreciated - again I stated I
am a Computer Programmer using SQL code in ASP pages mostly not much done
with SQL Server, I would have done this in VBScript (which I have already
done before) but store procedures are more efficient and reliable.
Oh and the comment about doing design and research on the data, you have no
idea how much design and research in this database I have done. With a
database that all the stored procedures are encrypted (which you cannot
decrypt unless you want to break the software agreement which I play by the
rules maybe you do not), no manuals or references on the database (because i
t
is part of a software program and while they want you to add functionalilty
to the product for your own uses they are not forth coming with how to do
things) and myself being no where near a DBA I think I have a pretty good
understanding of this database which you clearly do not because you did not
ask!
Thanks but no Thanks for your post...this is why I originally did not post
the code because people like you want to just code bash instead of helping!
"--CELKO--" wrote:

> Why did you destroy Standard SQL behavior? Why are there more NULLs in
> one table than should be in an entire Fortune 500 accounting package?
> Do you really have a lot of data elements that are in Chinese and 255
> characters long? Why do you have data type prefixes on variable names,
> which is a violation of both good programming and ISO-11179? Why do you
> have numeric data elements in strings? What kind of total garbage are
> trying to get with things like "weight VARCHAR(50)", "@.phonetype
> VARCHAR(50)", etc. And you don't seem to be aware of floating
> point rounding errors (does your machine have a floating point
> processor, or do you want to slow things down with a software floating
> point package?)
> You keep height in inches or cm then convert it for display. You do
> not do this in the database. The syntax for CAST is CAST (<exp> AS
> <datatype> ) -- do not use the proprietary CONVERT().
> You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
> But even worse, you did absolutely no design or research on the data.
> Other products have a MERGE or UPSERT statement to do this. The usual
> pattern in older products is:
> BEGIN
> -- insert the new rows
> INSERT INTO Foobar
> SELECT *
> FROM WorkingData AS W
> WHERE W.keycol
> NOT IN (SELECT keycol FROM Foobar);
> -- update the rows are already there
> UPDATE Foobar
> SET <column>
> = (SELECT col FROM WorkingData AS W
> WHERE W.keycol = Foobar.keycol)
> WHERE keycol IN (SELECT keycol FROM WorkingData);
> END;
>

CURSOR FETCH STATEMENT IS HANGING

Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:

> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>

Cursor Fetch Problem

I'm using a cursor to get a rowset and then update that rowset with certain new values. So I have a basic while loop to check until @.@.fetch_status is not zero. However, this means that it tries to fetch one time a row that does not exist before it exists the loop. This gives out an error message that the query had an error in it. I know it's not an error, but is there any way way to avoid that? Is there a way to peek ahead to see if I'm currently fetching the last row and exit before trying to fetch another one? Maybe there is a way to see how many rows were returned when the cursor was opened? This is a dynamic cursor so @.@.CURSOR_ROWS won't work as far as I know.

Thank you.What was the exact error?
For more information on cursors refer to books online.|||How about using the dreaded GOTO

WHILE 1 = 1

BEGIN

FETCH NEXT..............

IF (@.@.FETCH_STATUS <> 0) GOTO CloseCursor

......update rowset.......

END

CloseCursor:

CLOSE .........
DEALLOCATE ..........

GWsql

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:
>