August 1, 2008 at 7:37 am
Does anyone know how to convert Cursor logic into a CTE?
August 1, 2008 at 7:46 am
It would depend on what you are doing in the cursor as to wether or not you could convert it to a CTE and/or how you would write the CTE, or if using a CTE is the way to go. Why don't you post a specific situation and see what you get.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 7:53 am
I'm using Sql Server 2005
I got the idea of converting my cursor into a CTE from this website:
Here is my cursor logic:
DECLARE SALESNBR_Cursor CURSOR
FOR
SELECT DISTINCTOriginalHeaderNumber,
ModifiedHeaderNumber,
UseFlag,
OriginalCancellationDateid
FROM USER_STAGE.dbo.DW_REBOOK_CUSTOMER
WHERE OriginalCancellationDateid = @AS400DATEID
AND UseFlag = 1
ORDER BY OriginalHeaderNumber, OriginalCancellationDateid, ModifiedHeaderNumber
OPEN SALESNBR_Cursor
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MTO1FLAG = 0
SELECT @MTO1FLAG = CASE WHEN MSALESNBR IS NULL THEN 0
ELSE MSALESNBR END
FROMUSER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHEREMSALESNBR = @MSALESNBR
SET @1TOMFLAG = 0
SELECT @1TOMFLAG = CASE WHEN SALESNBR IS NULL THEN 0
ELSE SALESNBR END
FROMUSER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERESALESNBR = @SALESNBR
AND @MSALESNBR > MSALESNBR
AND CancelRebookFlag in (1,3)
SET @PROCESSID = 'Type1'
SELECT@PROCESSID = CASE WHEN ISNULL(convert(varchar,SALESNBR),'NOTINTABLE') = 'NOTINTABLE'
THEN 'Type1'
WHEN cancelrebookflag = 2 and ISNULL(convert(varchar,SALESNBR),'NOTINTABLE') <> 'NOTINTABLE'
THEN 'Type3'
END
FROMEINSTEIN.dbo.MY_BOOKING_FACT_EXT_REBOOK_XREF -- short term solution to stop duplicates
WHERESALESNBR = @SALESNBR
AND CancelRebookFlag = 2
IF @MTO1FLAG <> 0
BEGIN
UPDATEUSER_STAGE.DBO.DW_REBOOK_CUSTOMER
SETUseFlag = 0,
UseDesc = 'TFERXREF_TABLE_MSALESNBR'
WHEREOriginalHeaderNumber = @SALESNBR
END
IF @1TOMFLAG <> 0
BEGIN
UPDATEUSER_STAGE.DBO.DW_REBOOK_CUSTOMER
SETUseFlag = 0,
UseDesc = 'TFERXREF_TABLE_SALESNBR'
WHEREOriginalHeaderNumber = @SALESNBR
AND ModifiedHeaderNumber = @MSALESNBR
END
IF @PROCESSID = 'Type1' AND @MTO1FLAG = 0 AND @1TOMFLAG = 0
BEGIN
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
@SALESNBR as SALESNBR,
@SALESNBR as OSALESNBR,
@MSALESNBR as MSALESNBR,
1as CancelRebookFlag,
@CATEGORY as Category,
@SALESDATE as SALESDATE
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
@MSALESNBR as SALESNBR,
@SALESNBR as OSALESNBR,
0 as MSALESNBR,
2 as CancelRebookFlag,
@CATEGORY as Category,
@SALESDATE as SALESDATE
UPDATEUSER_STAGE.DBO.DW_REBOOK_CUSTOMER
SETUseFlag = 2,
UseDesc = 'TYPE1_PROCESS'
WHEREOriginalHeaderNumber = @SALESNBR
AND OriginalCancellationDateid = @SALESDATE
END
IF @PROCESSID = 'Type3' AND @MTO1FLAG = 0 AND @1TOMFLAG = 0
BEGIN
SELECT@OSALESNBR = OSALESNBR
FROM USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERESALESNBR = @SALESNBR
AND CancelRebookFlag = 2
UPDATE USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SETMSalesnbr = @MSALESNBR,
CancelRebookFlag = 3,
AS400DATEID = @SALESDATE
FROMUSER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERESALESNBR = @SALESNBR
AND CancelRebookFlag = 2
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT@MSALESNBR as SALESNBR,
@OSALESNBR as OSALESNBR,
0 as MSALESNBR,
2 as CancelRebookFlag,
@CATEGORY as Category,
@SALESDATE as SALESDATE
UPDATEUSER_STAGE.DBO.DW_REBOOK_CUSTOMER
SETUseFlag = 2,
UseDesc = 'TYPE3_PROCESS'
WHEREOriginalHeaderNumber = @SALESNBR
AND OriginalCancellationDateid = @SALESDATE
END
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
END
CLOSE SALESNBR_Cursor
DEALLOCATE SALESNBR_Cursor
END
August 1, 2008 at 9:15 pm
KMM (8/1/2008)
I'm using Sql Server 2005I got the idea of converting my cursor into a CTE from this website:
Nah... don't bother changing it if you're just going to use recurrsion. Recursive CTE's are just about as bad as cursors. Certainly, they are RBAR and not set based.
Best thing to do is figure out what you really want to do and then find the good and proper set based solution. Just because there's no cursor or While loop, doesn't necessarily mean there's no RBAR. Recursive CTE's, correlated sub-queries, and triangular joins are all forms of RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2008 at 12:30 am
Try this:
--====== Change Cursor to Temp table
Select Identity() as ID
, *
--move all of the temp variables here too
, Coalesce((SELECT MSALESNBR
FROM USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERE MSALESNBR = TMSalesNbr
), 0) as TMTo1Flag
, Coalesce((SELECT SALESNBR
FROM USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERE SALESNBR = TSalesNbr
AND TMSalesNbr > MSALESNBR
AND CancelRebookFlag in (1,3)
), 0) as T1ToMFlag
, Coalesce( (SELECT CASE WHEN ISNULL(convert(varchar,SALESNBR),'NOTINTABLE') = 'NOTINTABLE'
THEN 'Type1'
WHEN cancelrebookflag = 2 and ISNULL(convert(varchar,SALESNBR),'NOTINTABLE') <> 'NOTINTABLE'
THEN 'Type3'
END
FROM EINSTEIN.dbo.MY_BOOKING_FACT_EXT_REBOOK_XREF -- short term solution to stop duplicates
WHERE SALESNBR = TSalesNbr
AND CancelRebookFlag = 2
), 'Type1') as TProcessID
Into #SalesNbr
From (SELECT DISTINCT OriginalHeaderNumber as TSalesNbr
, ModifiedHeaderNumber as TMSalesNbr
, UseFlag as TUseFlag
, OriginalCancellationDateid as TSalesDate
FROM USER_STAGE.dbo.DW_REBOOK_CUSTOMER
WHERE OriginalCancellationDateid = @AS400DATEID
AND UseFlag = 1
ORDER BY OriginalHeaderNumber, OriginalCancellationDateid, ModifiedHeaderNumber
)
--======
OPEN SALESNBR_Cursor
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
WHILE @@FETCH_STATUS = 0
BEGIN
--
Update u
Set UseFlag = 0,
UseDesc = Case 'TFERXREF_TABLE_MSALESNBR'
From User_Stage.dbo.DW_ReBook_Customer u
Join #SalesNbr t ON OriginalHeaderNumber = TSalesNbr
Where (TMTo1Flag <> 0)
Update u
Set UseFlag = 0,
UseDesc = 'TFERXREF_TABLE_SALESNBR'
From User_Stage.dbo.DW_ReBook_Customer u
Join #SalesNbr t ON OriginalHeaderNumber = TSalesNbr
And ModifiedHeaderNumber = TMSalesNbr
Where T1ToMFlag <> 0
--
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
TSalesNbr as SALESNBR,
TSalesNbr as OSALESNBR,
TMSalesNbr as MSALESNBR,
1 as CancelRebookFlag,
@CATEGORY as Category,
TSalesDate as SALESDATE
From #SalesNbr
Where TProcessID = 1
And TMTo1Flag = 0
And T1ToMFlag = 0
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
TMSalesNbr as SALESNBR,
TSalesNbr as OSALESNBR,
0 as MSALESNBR,
2 as CancelRebookFlag,
@CATEGORY as Category,
TSalesDate as SALESDATE
From #SalesNbr
Where TProcessID = 1
And TMTo1Flag = 0
And T1ToMFlag = 0
UPDATE u
SET UseFlag = 2,
UseDesc = 'TYPE1_PROCESS'
From USER_STAGE.DBO.DW_REBOOK_CUSTOMER u
Join #SalesNbr t ON OriginalHeaderNumber = TSaleNbr
AND OriginalCancellationDateid = TSalesDate
Where TProcessID = 1
And TMTo1Flag = 0
And T1ToMFlag = 0
--
UPDATE USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SET MSalesnbr = TMSalesnbr,
CancelRebookFlag = 3,
AS400DATEID = TSalesDate
FROM USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
Join #SalesNbr t ON SALESNBR = TSalesNbr
AND CancelRebookFlag = 2
Where TProcessID = 'Type3' AND TMTo1Flag = 0 AND T1ToMFlag = 0
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT TMSalesNbr as SALESNBR,
(SELECT OSALESNBR
FROM USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
WHERE SALESNBR = TSalesNbr
AND CancelRebookFlag = 2
) as TOSalesNbr
0 as MSALESNBR,
2 as CancelRebookFlag,
@CATEGORY as Category,
TSalesDate as SALESDATE
From #SalesNbr
Where TProcessID = 'Type3' AND TMTo1Flag = 0 AND T1ToMFlag = 0
UPDATE u
SET UseFlag = 2,
UseDesc = 'TYPE3_PROCESS'
From USER_STAGE.DBO.DW_REBOOK_CUSTOMER u
Join #SalesNbr t ON OriginalHeaderNumber = TSalesNbr
AND OriginalCancellationDateid = TSalesDate
Where TProcessID = 'Type3' AND TMTo1Flag = 0 AND T1ToMFlag = 0
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2008 at 9:17 am
Barry... you might want to look for the word "Cursor" in the above script... 😀 Heh, late nite posts get me, too! 😉
Other than that, nice conversion! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2008 at 10:44 am
Heh, right. The section with the cursor is not necessary, just delete it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 4, 2008 at 2:24 pm
Thank you for the revised code. But the code isn't behaving correctly with the data.
I have tried various temp tables, but this is where it is tricky, which is why it was in a cursor, each row that it processes depending on the way the flag is set will determine the action that is done on that SalesNbr and MSAlesNbr, meaning each row is dependent on the previous row, which is why I haven't been able to figure out a way to update an entire column. That entire column can be updated and/or inserted wrong if it doesn't look at the previous row and know how many times that SalesNbr and MSAlesNbr exist. A SalesNbr and MSalesNbr can repeat itself so depending on how many times a SalesNbr appears and if it appears with the Same MSalesNbr or a different MsalesNbr can totally change the settings on the flags and rather not it is inserted into the Booking Fact table and if it is inserted rather it will have a CancelRebookFlag of 1 or 3. SalesNbr can have a 1:1, 1:M,and/or a M:1 match to MSalesNbr. I am trying to make it have a 1:1 match only, the rest doesn't get inserted into the table.
For example:
If I had this in my data:
OriginalHeaderNumber ModifiedHeaderNumber
28228147 32314812
28228147 32314813
The cursor would take the first row @SalesNbr = 28228147 @MSalesNbr = 32314812
@MTO1FLAG = 0, @1TOMFLAG = 0 and @PROCESSID = 'Type1' so it goes on to insert
BEGIN
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
@SALESNBR as SALESNBR,
@SALESNBR as OSALESNBR,
@MSALESNBR as MSALESNBR,
1as CancelRebookFlag,
@CATEGORY as Category,
@SALESDATE as SALESDATE
INSERT INTO USER_STAGE.dbo.BOOKING_FACT_EXT_CUSTOMER
SELECT
@MSALESNBR as SALESNBR,
@SALESNBR as OSALESNBR,
0 as MSALESNBR,
2 as CancelRebookFlag,
@CATEGORY as Category,
@SALESDATE as SALESDATE
UPDATEUSER_STAGE.DBO.DW_REBOOK_CUSTOMER
SETUseFlag = 2,
UseDesc = 'TYPE1_PROCESS'
WHEREOriginalHeaderNumber = @SALESNBR
AND OriginalCancellationDateid = @SALESDATE
END
this is how the table would look after the insert;
SalesNbr OSalesNbr MSalesNbr CancelRebookFlag Category SalesDate
28228147 28228147 32314812 1 Customer 20080804
32314812 28228147 0 2 Customer 20080804
then it goes and gets the second row:@SalesNbr = 28228147 @MSalesNbr =32314813
@MTO1FLAG = 0 but now @1TOMFLAG does not = 0
@SalesNbr 28228147 already exist in the table as SalesNbr because we just inserted it above
and @MSalesNbr 32314813 is greater than 32314812. Therefore this record doesn't get inserted.
August 4, 2008 at 3:04 pm
KMM (8/4/2008)
I have tried various temp tables, but this is where it is tricky, which is why it was in a cursor, each row that it processes depending on the way the flag is set will determine the action that is done on that SalesNbr and MSAlesNbr, meaning each row is dependent on the previous row, which is why I haven't been able to figure out a way to update an entire column.
I see no evidence of this in the code that you posted. the @SALSNBR and @MSALESNBR variables are re-loaded by the FETCHes each time through, they are not retaining values from the previous FETCH:
...
OPEN SALESNBR_Cursor
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
END
CLOSE SALESNBR_Cursor
DEALLOCATE SALESNBR_Cursor
That entire column can be updated and/or inserted wrong if it doesn't look at the previous row and know how many times that SalesNbr and MSAlesNbr exist. A SalesNbr and MSalesNbr can repeat itself so depending on how many times a SalesNbr appears and if it appears with the Same MSalesNbr or a different MsalesNbr can totally change the settings on the flags and rather not it is inserted into the Booking Fact table and if it is inserted rather it will have a CancelRebookFlag of 1 or 3.
OK, I am not seeing this in your code, so you are going to have to point it out to me.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 12:31 pm
DECLARE SALESNBR_Cursor CURSOR
FOR
SELECT DISTINCT OriginalHeaderNumber,
ModifiedHeaderNumber,
UseFlag,
OriginalCancellationDateid
FROM USER_STAGE.dbo.DW_REBOOK_CUSTOMER
WHERE OriginalCancellationDateid = @AS400DATEID
AND UseFlag = 1
ORDER BY OriginalHeaderNumber, OriginalCancellationDateid, ModifiedHeaderNumber
OPEN SALESNBR_Cursor
FETCH NEXT FROM SALESNBR_Cursor INTO @SALESNBR, @MSALESNBR, @USEFLAG, @SALESDATE
The @SalesNbr and @MsalesNbr variables are re-loaded after it goes thru all of the inserts and updates that is all surrounded by a BEGIN....END.
WHILE @@FETCH_STATUS = 0
BEGIN
But I am now looking into putting this into an SSIS package.
August 5, 2008 at 1:24 pm
OK.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 1:27 pm
I want to thank everyone for there assistance.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply