April 25, 2011 at 12:39 pm
I am getting this error message and cannot find a problem. Please help.
Error msg: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Here is my code:
declare @STARTINT,
@ENDINT,
@SSNINT
declare SEQ_List cursor for
SELECT * FROM LAYOFFS_2011 order by SSN;
OPEN SEQ_List
SET @START = 0
SET @END = (SELECT COUNT(*) FROM LAYOFFS_2011)
FETCH NEXT FROM SEQ_List
INTO @START, @END, @SSN;
WHILE @@FETCH_STATUS = 0 and @START<=@END
BEGIN
PRINT cast(@START as char(10)) + ' ' + CAST(@END AS CHAR(5))+ ' ' +cast(@SSN as char(10))
UPDATE LAYOFFS_2011 SET
SEQ=(SELECT ROW_NUMBER() OVER(PARTITION BY SSN ORDER BY SSN ASC)-1 FROM LAYOFFS_2011 )
WHERE SSN=@SSN
SET @START = @START +1
END
FETCH NEXT FROM SEQ_List
INTO @START, @END, @SSN
CLOSE SEQ_List
DEALLOCATE SEQ_List
GO
April 25, 2011 at 1:25 pm
you have been bit by the "select * " monster !!!!!!
Specify the columns you want to use and you'll be ok.
Try to avoid the cursor in the first place.
did you try and double check something like this ?
update a
set SEQ = Row_0_offset
from (
select *
, ROW_NUMBER() OVER ( PARTITION BY SSN ORDER BY SSN ASC ) - 1 Row_0_offset
from #LAYOFFS_2011
) a
Select *
from #LAYOFFS_2011
order by SSN
, SEQ
Keep in mind , because you only order by SSN, your results may vary from execution to execution ! If that random order is ok for you, no problem.
Else add another column to help you get consistent results
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 26, 2011 at 6:05 am
Thank you very much, I did not think of it.
This will do the trick.
Monika
April 26, 2011 at 9:51 am
you only have 3 items in the INTO section
but you SELECTed * from the table
unless the table only has three columns,
this presents a problem
just SELECT COLUMN,COLUMN,COLUMN
FROM table
just the three columns you need
April 26, 2011 at 10:46 am
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply