May 29, 2008 at 11:54 am
I have the following code that I am trying to use the Tally table method to make this more set based process and I am not sure how to approach this.
Right now I select the rows I want to process into a temp table and then I start a while to read one row at a time because I have to process each row setting flags for it by a series of select,insert,update, deletes depending on what checks that one row passes, then it goes and selects the next row from the temp table and does processing on it until it reaches the end of the row.
USE TempDB
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Conditionally drop
IF OBJECT_ID('dbo.temp') IS NOT NULL
DROP TABLE dbo.temp
CREATE TABLE [dbo].[temp]
(
[iRowID] [int] IDENTITY(1,1) NOT NULL,
[OHNumber] [int] NOT NULL,
[MHNumber] [int] NOT NULL,
[UseFlag] [int] NOT NULL,
[OCDateID] [int] NOT NULL
CONSTRAINT PK_temp_iRowID PRIMARY KEY CLUSTERED (iRowID) --WITH FILLFACTOR = 50
)
CREATE TABLE [dbo].[CUSTOMER]
(
[OCDateID] [int] NULL,
[OBDateID] [int] NOT NULL,
[MBDateID] [int] NOT NULL,
[OINumber] [int] NULL,
[MINumber] [int] NULL,
[OHNumber] [int] NULL,
[MHNumber] [int] NULL,
[CustomerID] [int] NULL,
[UseFlag] [int] NULL,
[UseDesc] [varchar](50) NULL
)
CREATE TABLE [dbo].[REBOOK_CUSTOMER]
(
[SalesNbr] [numeric](9, 0) NOT NULL,
[OSalesNbr] [numeric](9, 0) NOT NULL,
[MSalesNbr] [numeric](9, 0) NOT NULL,
[CancelRebook] [tinyint] NOT NULL,
[Category] [varchar](50) NULL,
[DateID] [int] NULL
)
INSERT INTO REBOOK_CUSTOMER (SalesNbr,OSalesNbr,MSalesNbr,CancelRebook,Category,DateID)
SELECT 55972038,55972038,55972410,1,'Customer',20080515 UNION ALL
SELECT 55972410,55972038,0,2,'Customer',20080515 UNION ALL
SELECT 55972086,55972086,55977818,1,'Customer',20080515
INSERT INTO CUSTOMER (OCDateID,OBDateID,MBDateID,OINumber,MINumber,OHNumber,MHNumber,CustomerID,UseFlag,UseDesc)
SELECT 20080528,20071129,20080528,50931435,56439558,50931435,56439558,32049789,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071129,20080528,50931435,56439620,50931435,56439620,32049789,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071129,20080528,50931435,56439558,50931436,56439558,32049789,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071129,20080528,50931435,56439620,50931436,56439620,32049789,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56433995,51383662,56433995,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434122,51383662,56434122,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434154,51383662,56434154,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56433995,51383663,56433995,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434122,51383663,56434122,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434154,51383663,56434154,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56433995,51383664,56433995,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434122,51383664,56434122,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383662,56434154,51383664,56434154,32338442,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383845,56434613,51383845,56434613,32338470,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383845,56434694,51383845,56434694,32338470,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383845,56434613,51383846,56434613,32338470,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20071218,20080528,51383845,56434694,51383846,56434694,32338470,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20080130,20080528,52492157,56440101,52492157,56440101,32749075,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20080130,20080528,52492157,56440101,52492157,56440102,32749075,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080528,20080130,20080528,52492157,56440101,52492157,56440103,32749075,1,'AVAILABLE_TO_PROCESS' UNION ALL
SELECT 20080521,20080130,20080528,52492157,56440402,52492157,56440402,32749075,0,'CRF1 Process' UNION ALL
SELECT 20080522,20080130,20080528,52492157,56440101,52492157,56440102,32749075,0,'REBOOKCUST MSALESNBR' UNION ALL
SELECT 20080523,20080130,20080528,52492157,56440101,52492157,56440103,32749075,0,'CRF1 Process' UNION ALL
SELECT 20080524,20080130,20080528,52492157,56440402,52492157,56440402,32749075,0,'CRF1 Process'
INSERT INTO temp
SELECT OHNumber
,MHNumber
,UseFlag
,OCDateID
FROM CUSTOMER WITH (NOLOCK)
WHERE OCDateID = 20080528
AND UseFlag = 1
ORDER BY OHNumber
,MHNumber
DECLARE@iReturnCode int
,@iNextRowID int
,@iCurrentRowID int
,@iLoopControl int
,@chProductNumber nchar(30)
,@iSalesNbr int
,@iOSalesNbr int
,@iMSalesNbr int
,@iUseFlag int
,@iSalesDateint
,@vProcessIDvarchar(10)
,@iMto1Flagint
,@i1toMFlagint
--Initialize Variables
SELECT @iLoopControl = 1
SELECT @iNextRowID = min(iRowID)
FROM temp
--Make sure the table has data.
IF ISNULL(@iNextRowID,0) = 0
BEGIN
SELECT 'No data is found in table.'
RETURN
END
--Retrieve the first row
SELECT@iCurrentRowID = iRowID
,@iSalesNbr = OHNumber
,@iMSalesNbr = MHNumber
,@iUseFlag = UseFlag
,@iSalesDate = OCDateID
FROM temp
WHERE iRowID = @iNextRowID
--Start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
--This is where I perform my detailed row-by-row processing.
/*========================================================================================================
Determine if the current record has a M:1 relationship based on the modified sales number
Does the MSalesNbr already exist in REBOOK_CUSTOMER
FLAG CHECK 1
=========================================================================================================*/
SET @iMto1Flag = 0
SELECT @iMto1Flag = CASE WHEN MSalesNbr IS NULL THEN 0 ELSE MSalesNbr END
FROM REBOOK_CUSTOMER WITH (NOLOCK)
WHERE MSalesNbr = @iMSalesNbr
/*=========================================================================================================
Determine if the current record has a 1:M relationship based on the sales number and modified sales number
Does the SalesNbr already exist in REBOOK_CUSTOMER
FLAG CHECK 2
==========================================================================================================*/
SET @i1toMFlag = 0
SELECT @i1toMFlag = CASE WHEN SalesNbr IS NULL THEN 0 ELSE SalesNbr END
FROM REBOOK_CUSTOMER WITH (NOLOCK)
WHERE SalesNbr = @iSalesNbr
AND @iMSalesNbr > MSalesNbr
AND CancelRebook in (1,3)
/*==========================================================================================================
Determine if the current SalesNbr should be processed as a new cancel rebook (CRF1) or as a change (CRF3)
PROCESSFLAG CHECKS
===========================================================================================================*/
SET @vProcessID = 'CRF1'
SELECT @vProcessID =
CASE WHEN isnull(convert(varchar,SalesNbr),'NOTINTABLE') = 'NOTINTABLE' THEN 'CRF1'
WHEN CancelRebook = 2 AND isnull(convert(varchar,SalesNbr),'NOTINTABLE') <> 'NOTINTABLE' THEN 'CRF3'
END
FROM MY_BOOKING_ WITH (NOLOCK) -- short term solution to stop duplicates
WHERE SalesNbr = @iSalesNbr
AND CancelRebook = 2
/*=========================================================================================================
If the currently processed sales number has a Mto1 relationship, set the use flag to 0 and set the use
description accordingly
==========================================================================================================*/
IF @iMto1Flag <> 0
BEGIN
UPDATE CUSTOMER
SET UseFlag = 0 --do not process
,UseDesc = 'REBOOKCUST MSALESNBR' --'TFERXREF_TABLE_MSALESNBR'
WHERE OHNumber = @iSalesNbr
AND MHNumber = @iMSalesNbr --56439558
DELETE FROM dbo.temp
WHERE OHNumber = @iSalesNbr
AND MHNumber = @iMSalesNbr --56439558
END
/*========================================================================================================
If the currently processed sales number has a 1toM relationship, set the use flag to 0 and set the use
description accordingly
=========================================================================================================*/
IF @i1toMFlag <> 0
BEGIN
UPDATE CUSTOMER
SET UseFlag = 0
,UseDesc = 'REBOOKCUST SALESNBR' --'TFERXREF_TABLE_SALESNBR'
WHERE OHNumber = @iSalesNbr
AND MHNumber = @iMSalesNbr
DELETE FROM dbo.temp
WHERE OHNumber = @iSalesNbr
AND MHNumber = @iMSalesNbr
END
/*=======================================================================================================
Process Type 1 records first.
The following steps will also create the corresponding type 2 record
=======================================================================================================*/
IF @vProcessID = 'CRF1' --'Type1'
AND @iMto1Flag = 0 AND @i1toMFlag = 0
BEGIN
INSERT INTO REBOOK_CUSTOMER
SELECT @iSalesNbr AS SalesNbr
,@iSalesNbr AS OSalesNbr
,@iMSalesNbr AS MSalesNbr
,1 AS CancelRebook
,'Customer' AS Category
,@iSalesDate AS SalesDate
INSERT INTO REBOOK_CUSTOMER
SELECT @iMSalesNbr AS SalesNbr
,@iSalesNbr AS OSalesNbr
,0AS MSalesNbr
,2AS CancelRebook
,'Customer' AS Category
,@iSalesDate AS SalesDate
UPDATE CUSTOMER
SET UseFlag = 2
,UseDesc = 'CRF1 Process' --'TYPE1_PROCESS'
WHERE OHNumber = @iSalesNbr
AND OCDateID = @iSalesDate
END
/*============================================================================
Process Type 3 records.
============================================================================*/
IF @vProcessID = 'CRF3' --'Type3'
AND @iMto1Flag = 0 AND @i1toMFlag = 0
BEGIN
SELECT @iOSalesNbr = OSalesNbr
FROM REBOOK_CUSTOMER WITH (NOLOCK)
WHERE SalesNbr = @iSalesNbr
AND CancelRebook = 2
UPDATE REBOOK_CUSTOMER
SET MSalesnbr = @iMSalesNbr
,CancelRebook = 3
,DateID = @iSalesDate
FROM REBOOK_CUSTOMER
WHERE SalesNbr = @iSalesNbr
AND CancelRebook = 2
INSERT INTO REBOOK_CUSTOMER
SELECT @iMSalesNbr AS SalesNbr
,@iOSalesNbr AS OSalesNbr
,0AS MSalesNbr
,2AS CancelRebook
,'Customer' AS Category
,@iSalesDate AS SalesDate
UPDATE CUSTOMER
SET UseFlag = 2
,UseDesc = 'CRF3 Process'
WHERE OHNumber = @iSalesNbr
AND OCDateID = @iSalesDate
END
--Reset looping variables
SELECT @iNextRowID = NULL
--Get the Next iRowID
SELECT @iNextRowID = min(iRowID)
FROM temp
WHERE iRowID > @iCurrentRowID
--Did we get a valid next row ID?
IF ISNULL(@iNextRowID,0) = 0
BEGIN
BREAK
END
--Get the next row
SELECT@iCurrentRowID = iRowID
,@iSalesNbr = OHNumber
,@iMSalesNbr = MHNumber
,@iUseFlag = UseFlag
,@iSalesDate = OCDateID
FROM temp
WHERE iRowID = @iNextRowID
END
RETURN
May 29, 2008 at 12:15 pm
Your script isn't complete. Where/How does @iMto1Flag get set? What are the other INSERT/UPDATE statements and the logic that determines which is used?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 29, 2008 at 12:20 pm
The @iMto1Flag gets set under the piece that says this:
--This is where you perform your detailed row-by-row processing.
The rest of the other insert/updates stmts are lengthy, I didn't include it because what I was looking for was how to redo this without using the WHILE so that it is more set based approached. I will edit it now and add the rest of code.
Thank you for your quick response.
May 29, 2008 at 1:20 pm
OK, here's everything EXCEPT the handling of TYPE 3.
I've got to get back to work here, so hopefully someone can help with the rest.
This uses no Cursors, I haven't tested it, but think it's right. Give it a try and see if it gives you the results you want....
/*========================================================================================================
Determine if the current record has a M:1 relationship based on the modified sales number
Does the MSalesNbr already exist in REBOOK_CUSTOMER
FLAG CHECK 1
--- This can all be done in a single update statement using a CASE statement to handle the logic
=========================================================================================================*/
UPDATE Customer
SET
UseFlag = CASE WHEN MSalesNbr IS NULL THEN UseFlag ELSE 0 END
,UseDesc = CASE WHEN MSalesNbr IS NULL THEN UseDesc ELSE 'REBOOKCUST MSALESNBR' END --'TFERXREF_TABLE_MSALESNBR'
FROM
dbo.Customer AS c
INNER JOIN dbo.REBOOK_CUSTOMER AS r WITH (NOLOCK)
ON c.MHNumber = r.MSalesNbr
WHERE OCDateID = 20080526
AND UseFlag = 1
/*=========================================================================================================
Determine if the current record has a 1:M relationship based on the sales number and modified sales number
Does the SalesNbr already exist in REBOOK_CUSTOMER
FLAG CHECK 2
---- This can all be done in a single update statement using a CASE statement to handle the logic
==========================================================================================================*/
UPDATE CUSTOMER
SET
UseFlag = CASE WHEN SalesNbr IS NULL THEN UseFlag ELSE 0 END
,UseDesc = CASE WHEN SalesNbr IS NULL THEN UseDesc ELSE 'REBOOKCUST SALESNBR' END --'TFERXREF_TABLE_SALESNBR'
FROM
dbo.Customer AS c
INNER JOIN dbo.REBOOK_CUSTOMER AS r WITH (NOLOCK)
ON c.MHNumber = r.MSalesNbr
AND c.OHNumber = r.SalesNbr
WHERE
OCDateID = 20080526
AND UseFlag = 1
AND CancelRebook in (1,3)
/*=======================================================================================================
Process Type 1 records first.
The following steps will also create the corresponding type 2 record
=======================================================================================================*/
-- now you insert the missing TYPE 1 records
INSERT INTO REBOOK_CUSTOMER
SELECT
OHNumber AS SalesNbr
,OHNumber AS OSalesNbr
,MHNumber AS MSalesNbr
,1 AS CancelRebook
,'Customer' AS Category
,OCDateID AS SalesDate
FROM TempDB.dbo.CUSTOMER WITH (NOLOCK)
WHERE
OCDateID = 20080526
AND UseFlag = 1
AND MSalesNbr IS NULL
AND ISNULL(CONVERT(VARCHAR,SalesNbr),'NOTINTABLE') = 'NOTINTABLE'
UNION ALL
SELECT
MHNumber AS SalesNbr
,OHNumber AS OSalesNbr
,0 AS MSalesNbr
,2 AS CancelRebook
,'Customer' AS Category
,OCDateID AS SalesDate
FROM TempDB.dbo.CUSTOMER WITH (NOLOCK)
WHERE
OCDateID = 20080526
AND UseFlag = 1
AND MSalesNbr IS NULL
AND ISNULL(CONVERT(VARCHAR,SalesNbr),'NOTINTABLE') = 'NOTINTABLE'
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 29, 2008 at 3:19 pm
I'm still testing the code, and going thru it line by line, it's kind of tricky. When I first wrote this code I thought inner join would be the way, but I have to be careful because both OHNumber and MHNumber can duplicated.
For Example I want to make sure both rows are processed even though the OHNumber is duplicated:
OHNumber MHNumber
50931435 56439558
50931435 56439620
And Vice versa for this example with the MHNumber being duplicated
OHNumber MHNumber
50931436 56439558
50931436 56439620
50931435 56439620
Both examples are valid rows that I would want to process.
May 30, 2008 at 8:47 am
I'm not quite sure if I understand how you are combining things for the INSERTing of Process Type 1. that table doesnt have a MSalesNbr as a column
May 30, 2008 at 8:57 am
Would it be best not to use the temp table I created at the beginning:
INSERT INTO temp
SELECT OHNumber
,MHNumber
,UseFlag
,OCDateID
FROM TempDB.dbo.CUSTOMER WITH (NOLOCK)
WHERE OCDateID = 20080526
AND UseFlag = 1
ORDER BY OHNumber
,MHNumber
May 30, 2008 at 8:57 am
KMM (5/30/2008)
I'm not quite sure if I understand how you are combining things for the INSERTing of Process Type 1. that table doesnt have a MSalesNbr as a column
OOPS, it was a hectec day... take a stab at this.
INSERT INTO REBOOK_CUSTOMER
SELECT
OHNumber AS SalesNbr
,OHNumber AS OSalesNbr
,MHNumber AS MSalesNbr
,1 AS CancelRebook
,'Customer' AS Category
,OCDateID AS SalesDate
FROM TempDB.dbo.CUSTOMER WITH (NOLOCK)
INNER JOIN MY_BOOKING_ WITH (NOLOCK) -- short term solution to stop duplicates
ON SalesNbr = OHNumber
AND ISNULL(CONVERT(VARCHAR,SalesNbr),'NOTINTABLE') = 'NOTINTABLE'
AND CancelRebook = 2
WHERE
OCDateID = 20080526
AND UseFlag = 1
UNION ALL
SELECT
MHNumber AS SalesNbr
,OHNumber AS OSalesNbr
,0 AS MSalesNbr
,2 AS CancelRebook
,'Customer' AS Category
,OCDateID AS SalesDate
FROM TempDB.dbo.CUSTOMER WITH (NOLOCK)
INNER JOIN MY_BOOKING_ WITH (NOLOCK) -- short term solution to stop duplicates
ON SalesNbr = OHNumber
AND ISNULL(CONVERT(VARCHAR,SalesNbr),'NOTINTABLE') = 'NOTINTABLE'
AND CancelRebook = 2
WHERE
OCDateID = 20080526
AND UseFlag = 1
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 30, 2008 at 9:37 am
that code inserts 0 rows. let me put some sample data in there so you can see what I'm talking about.
i dont think something is write let me do somemore testing i may have to change some of the code.
May 31, 2008 at 12:52 pm
I have updated my code above, so that you can run it with sample code.
The creation of Tables: temp, Customer and Rebook_Customer have been added to original code above.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply