July 17, 2010 at 12:12 pm
Hello,
I am trying to compare values found in rows of my table and conditionally label each row. I need to increment the @DSCounter by 1 if the number in the cursor (@ACHVal) is <= the number in the previous row. I am sure I'm going about this completely backwards, please help.
USE database_1
GO
CREATE TABLE dbo.TesterACH (
Col1 int null,
Col2 int null,
Col3 int null,
Col4 int null
)
GO
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (1,5,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (2,5,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (3,6,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (4,7,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (5,5,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (6,5,9)
INSERT INTO dbo.TesterACH (Col1,Col3,Col4) Values (7,7,9)
GO
DECLARE @ACHVal int
DECLARE @PrevACHVal int
DECLARE @x int
DECLARE @DSCounter int
DECLARE @Seq int
SET @ACHVal = 0 -- Current ACH Value for compare
SET @PrevACHVal = 0 --Prev ACH Value for compare
SET @DSCounter = 0 --used to label data set
SET @Seq = 0 --used to map row number for update
DECLARE c_ACHValFinder Cursor
FOR
SELECT Col3 FROM dbo.TesterACH
OPEN c_ACHValFinder
WHILE @@Fetch_STATUS = 0
BEGIN
Fetch NEXT from c_ACHValFinder INTO @ACHVal
Print @ACHVal
--SET @ACHVal = @PrevACHVal
------------------------------------------------
--Use this space to operate second cursor
DECLARE c_prevACHValFinder Cursor
FOR
SELECT Col1, Col3 FROM dbo.TesterACH
OPEN c_prevACHValFinder
PRINT @ACHVal
PRINT @PrevACHVal
WHILE @x<9
IF @ACHVal <= @PrevACHVal
BEGIN
PRINT ' inside the second cursor'
UPDATE dbo.TesterACH
SET Col2 = @DSCounter
WHERE Col1 = @Seq
Fetch NEXT from c_prevACHValFinder INTO @Seq, @PrevACHVal
PRINT @PrevACHVal
END
ELSE
BEGIN
PRINT 'ELSE CONDITION MET'
SET @DSCounter = @DSCounter +1
UPDATE dbo.TesterACH
SET Col2 = @DSCounter
WHERE Col1 = @Seq
SET @PrevACHVal = @ACHVal
END
SET @PrevACHVal = @ACHVal
CLOSE c_prevACHValFinder
DEALLOCATE c_prevACHValFinder
-------------------------------------------------
END
Close c_ACHValFinder
DEALLOCATE c_ACHValFinder
Print @DSCOUNTER
Print 'DSCOUNTER = 6? IF SO SUCCESS!'
SELECT *
FROM dbo.TesterACH
July 17, 2010 at 1:04 pm
Hi Tim
Could you please try to explain what you want to do with your data instead of what your code shall do?
Your code has some issues with the cursors (in general and in specific :-P). The code within the cursors will never be touched because you check for @@FETCH_STATUS before it was ever set.
Could you provide something like:
If column "Col1" is less than xyz, than its Col2 has to become xx.
Thanks
Flo
July 17, 2010 at 1:22 pm
Hi Flo,
I'm attempting to update col2. It is currently null. The updated value should be assigned to +1 each time Col3 is less than the record before it, if the previous records value is equal or greater than the previous record, then I want to assign the same value to col2. Col1 is the ordering.
Thanks,
Tim
July 17, 2010 at 1:45 pm
Hi
Give this a try. (This requires your Col1 to be the primary key. If not so, is there another index on Col1?)
IF (OBJECT_ID('tempdb..#TesterACH') IS NOT NULL)
DROP TABLE #TesterACH;
CREATE TABLE #TesterACH (
Col1 int not null PRIMARY KEY CLUSTERED,
Col2 int null,
Col3 int null,
Col4 int null
);
GO
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (1,5,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (2,5,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (3,6,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (4,7,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (5,5,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (6,5,9)
INSERT INTO #TesterACH (Col1,Col3,Col4) Values (7,7,9)
GO
DECLARE @counter INT;
SELECT @counter = 0;
-- update rows
UPDATE t1 SET
t1.Col2 = @counter
,@counter = CASE WHEN t1.Col3 < t2.Col3 THEN @counter + 1 ELSE @counter END
FROM #TesterACH t1 WITH(INDEX(0), TABLOCKX)
JOIN #TesterACH t2 ON t1.Col1 - 1 = t2.Col1
-- show result
SELECT * FROM #TesterACH;
--SELECT
-- t1.*
-- ,t2.Col3
--FROM #TesterACH t1
-- JOIN #TesterACH t2 ON t1.Col1 - 1 = t2.Col1
--ORDER BY t1.Col1;
Greets
Flo
July 17, 2010 at 1:57 pm
Thanks so much Flo!
This works great! Just for my own understanding on the cursor though, how would I set the Fetch_Status?
Kind Regards,
Tim
July 17, 2010 at 2:03 pm
For your understanding with the cursor:
You have to call FETCH NEXT ... before you can access the FETCH_STATUS
For your understanding with databases:
Do never use cursors ;-). Your approach might work fast with 5 or 10 rows, but you run into huge performance problems when using cursors on larger counts of rows - especially when nesting cursors. As a personal advice, whenever you are working on a T-SQL procedure/script and you don't know how to handle the requirements without a cursor, start a new thread here. I'm sure there are always good people who will help you to find a better solution. 🙂
Greets
Flo
July 17, 2010 at 2:16 pm
Much Appreciated Flo.
Thank you!
July 17, 2010 at 9:31 pm
Flo,
The Quirky Update method is a valid approach here, but you cannot use a JOIN!
Also MAXDOP 1 must be specified to avoid parallelism.
Using your example table, I would write the update as:
DECLARE @counter INTEGER,
@lastCol3 INTEGER;
UPDATE T1
SET @counter =
CASE
WHEN -- First row, set counter to zero
@counter IS NULL
THEN 0
WHEN -- Condition met, increment the counter
T1.Col3 < @lastCol3
THEN @counter + 1
ELSE -- Condition not met, keep the same counter
@counter
END,
-- Set the value in Col2 to @counter
Col2 = @counter,
-- Remember the value in Col3 for the next time around
@lastCol3 = T1.Col3
FROM #TesterACH T1 WITH (INDEX(0), TABLOCKX) -- These hints are REQUIRED
OPTION (MAXDOP 1); -- This hint is REQUIRED
The full background and rules can be found in Jeff Moden's article:
http://www.sqlservercentral.com/articles/T-SQL/68467/
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 17, 2010 at 11:52 pm
Hi,
Why you are using cursors? it's degrade the performance of sql query and also consuming memory..
Use while loop instead of cursor. You can see the dramatic performance... In while loop you can increment or decrement counter as well as you can perform DML operations as well. So go to with while loop instead of cursor.
July 18, 2010 at 3:26 am
Paul, thanks for the correction!
July 18, 2010 at 3:45 am
Sagar Sawant (7/17/2010)
Why you are using cursors? it's degrade the performance of sql query and also consuming memory..Use while loop instead of cursor. You can see the dramatic performance... In while loop you can increment or decrement counter as well as you can perform DML operations as well. So go to with while loop instead of cursor.
That's a myth. While loops are generally no better or worse than an optimised cursor.
A set-based solution (such as the Quirky Update solution presented) will almost always outperform both by a very significant margin.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 18, 2010 at 11:12 am
Paul,
Thank you very much. I appreciate everyone's assistance with this.
July 18, 2010 at 12:44 pm
Sagar Sawant (7/17/2010)
Hi,Why you are using cursors? it's degrade the performance of sql query and also consuming memory..
Use while loop instead of cursor. [font="Arial Black"]You can see the dramatic performance[/font]... In while loop you can increment or decrement counter as well as you can perform DML operations as well. So go to with while loop instead of cursor.
Acually, No... you won't. A While loop is no better than a Forward Only, Read Only, Static Cursor.
Write you're While Loop code and the Cursor Code and I'll be happy to show you. And, not to worry... we'll keep it all very friendly because a lot of people believe in that same myth. I believe I can also speak for Paul when I say our only purpose is to educate.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2010 at 12:23 am
Jeff Moden (7/18/2010)
I believe I can also speak for Paul when I say our only purpose is to educate.
Educate, help, and learn 🙂
My apologies if my previous post seemed a little 'short' - I was pressed for time, not cross 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply