Cursors, cursors, CURSES!!!!
-
Hey all,
I thought I would challenge you with this little brain teaser....errrr....buster, as my head is ready to explode. My cursor is written fine, (and I wish I had the code to post but I am at home) but only has one Loop. I have illstrated what my issue is with the cursor below:
RowIDTerrProvFSACountGroupLabel
1EastNFAA113
2EastNFAA255AA2
3EastNFAA335
4EastNFAA423
5EastNFAA513
6EastNFAA635
7EastNFAA723
8EastNFAA823
9EastNFAA913
10EastNFAA1035
Cursor is created on those with null Label. Target Fetch should always be the first available of this criteria.
Fetch 1 Target Row
All data in RowID 1 into named variables (these are called 0 variables for now)
RowIDTerrProvFSACountGroupingLabel
1EastNFAA113
Fetch 2 Current Row
All data in RowID 3 into named variables (these are called 1 variables for now)
RowIDTerrProvFSACountGroupingLabel
3EastNFAA323
Compares variables 0 and 1 to ensure:
1.Terr = Terr
2.NF = NF
3.1st letter of FSA = 1st letter of FSA
4.Count + Count >= Grouping
These do not pass the criteria. Go to next record for fetch into current row (variables 1)
RowIDTerrProvFSACountGroupingLabel
4EastNFAA423
Compares variables 0 and 1 to ensure:
1.Terr = Terr
2.NF = NF
3.1st letter of FSA = 1st letter of FSA
4.Count + Count >= Grouping
These pass the criteria. Update Label to show:
RowIDTerrProvFSACountGroupingLabel
1EastNFAA113AA1AA4
2EastNFAA255AA2
3EastNFAA313
4EastNFAA423AA1AA4
-- This is where I am having problems.
Fetch Target Row (I want it to be RowID 4 as it is null, but it fetches 5)
RowIDTerrProvFSACountGroupingLabel
5EastNFAA513
Can anyone help me get the 0 variables to fetch next relative to its position, and not the position of the 1 variables? I am happy to post code tomorrow, but if anyone can help me for when I get into the office that would be awesome!!
Thanks in advance,
Steve
-
If you can post the code tomorrow, it will be much easier to look into. But you knew that 🙂
~I just posted this reply so that I would get an email of your next post~ FYI
______________________________________________________________________
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. Selburg -
Maybe it's that time of the night when my brain's all fuzzy but seems to me that your next row should be RowID 5 - unless what you want to do is reset the updated row to 0 - in which case you could try a "fetch prior"...and of course, your cursor is not a "forward only" one...
**ASCII stupid question, get a stupid ANSI !!!** -
Hey Jason and sushila,
Here is the code. I hate crusors as much as the next, but this table is gonna max out at about 1000 rows, so not too bad in performance. Anything you guys can offer is would be awesome. Also, its just not looping through the records as I would expect it to. Again, if you guys can help a guy out, that is much appreciated.
Thanks,
Steve
SET NOCOUNT ON
--Declare the cursor and the values to be represented in the FETCH
DECLARE FSA SCROLL CURSOR
FOR SELECT RowID, Territory, Province, Substring(FSA,1, 1) As FSA2, FSA, Count, Grouping, GroupLabel,
Count as TempCount, Grouping as TempGrouping
FROM dbo.FSAGroupingCursor
Where GroupLabel is null
ORDER BY Territory, Province, Substring(FSA,1, 1)
FOR UPDATE OF GroupLabel, Counter
--Declare the variables to store the values returned by FETCH.
DECLARE @RowIDint
DECLARE @Territoryvarchar (50)
DECLARE @Provincevarchar (50)
DECLARE @FSA2varchar (50)
DECLARE @FSAvarchar (50)
DECLARE @Countint
DECLARE @Groupingint
DECLARE @GroupLabelvarchar (50)
DECLARE @TempCountint
DECLARE @TempGroupingint
DECLARE @RowID1int
DECLARE @Territory1varchar (50)
DECLARE @Province1varchar (50)
DECLARE @FSA21varchar (50)
DECLARE @FSA1varchar (50)
DECLARE @Count1int
DECLARE @Grouping1int
DECLARE @GroupLabel1varchar (50)
DECLARE @TempCount1int
DECLARE @TempGrouping1int
--Start
OPEN FSA
--FETCH Target record from table
FETCH Next FROM FSA
INTO @RowID, @Territory, @Province, @FSA2, @FSA, @Count, @Grouping, @GroupLabel, @TempCount, @TempGrouping
Select @RowID, @Territory, @Province, @FSA2, @FSA, @Count, @Grouping, @GroupLabel, @TempCount, @TempGrouping
--Start inner loop
While @@FETCH_STATUS = 0
BEGIN
--FETCH Current record from table into second set of variables. This is the next record in the set
FETCH NEXT FROM FSA
INTO @RowID1, @Territory1, @Province1, @FSA21, @FSA1, @Count1, @Grouping1, @GroupLabel1, @TempCount1, @TempGrouping1
Select @RowID1, @Territory1, @Province1, @FSA21, @FSA1, @Count1, @Grouping1, @GroupLabel1, @TempCount1, @TempGrouping1
--Set the Temporary Store Count in Target FETCH to equal the Count total of both fetches
Set @TempCount = @TempCount + @TempCount1
--Set the TempGrouping to the Max value of TempGrouping from target and current.
--This looks at target @TempGrouping. If this is less than current TempGrouping
--then it sets target TempGrouping to current TempGrouping value. If target is greater,
--then it ignores.
IF @TempGrouping = @TempGrouping
--If the records pass, then it updates the GroupLabel in the table equal to the target FSA + current FSA.
BEGIN
UPDATE dbo.FSAGroupingCursor
SET Counter = 'Terr, Prov and FSA match',
GroupLabel = @FSA + @FSA1
WHERE RowID = @RowID
END
IF @Territory = @Territory1 and @Province = @Province1 and @FSA2 = @FSA21 and @TempCount >= @TempGrouping
--If the records pass, then it updates the GroupLabel in the table equal to the target FSA + current FSA.
BEGIN
UPDATE dbo.FSAGroupingCursor
SET Counter = 'Terr, Prov and FSA match',
GroupLabel = @FSA + @FSA1
WHERE RowID = @RowID1
END
--Fetches next record in table into target. Restarts Loop
END
FETCH NEXT FROM FSA
INTO @RowID, @Territory, @Province, @FSA2, @FSA, @Count, @Grouping, @GroupLabel, @TempCount, @TempGrouping
Select @RowID, @Territory, @Province, @FSA2, @FSA, @Count, @Grouping, @GroupLabel, @TempCount, @TempGrouping
CLOSE FSA
DEALLOCATE FSA
SET NOCOUNT OFF
-
Hey all,
Thanks again for the help. I re-wrote it using two cursors which served my purpose nicely.
Steve
-
Try this.
UPDATE FSA1
SET Counter = 'Terr, Prov and FSA match',
GroupLabel = Substring(FSA1.FSA,1, 1) + Substring(FSA2.FSA,1, 1)
FROM dbo.FSAGroupingCursor FSA1
INNER JOIN dbo.FSAGroupingCursor FSA2 ON FSA1.RowID <> FSA2.RowID
AND FSA1.Territory = FSA2.Territory1 and FSA1.Province = FSA2.Province1 and Substring(FSA1.FSA,1, 1)= Substring(FSA2.FSA,1, 1) and FSA1.Count >= FSA2.Grouping
Must be a little bit faster.
And the code is a little bit shorter...
_____________
Code for TallyGenerator -
Jason,
just in case you don't know - if you click on "Watch This Topic" link at the top of page, you will receive notifications of new posts even if you didn't post a reply.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply