October 10, 2008 at 9:12 am
Hello,
I have this nice cursor (yes, I know you should never use them but I feel I must broaden my horizon).
DECLARE @ColumnE As varchar(50)
DECLARE product_cursor CURSOR FOR
SELECT F2 FROM dbo.Products
FOR UPDATE OF F2
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ColumnE
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @ColumnE =
--(SELECT F2
--FROM products
--WHERE CURRENT OF product_cursor
--)
UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor
FETCH NEXT FROM product_cursor INTO @ColumnE
END
CLOSE product_cursor
DEALLOCATE product_cursor
The Issue is, as I have tested with a little "INSERT INTO test VALUES (@ColumnE)", the variable ColumnE always
seems to be the first row/one that was picked up at the courser declaration i.e.
the variable is not updated while going through the rows.
Does anyone have an idea why?
I built in a variable setter:
--SET @ColumnE =
--(SELECT F2
--FROM products
--WHERE CURRENT OF product_cursor
--)
But it does not work.
Line 23: CURRENT OF clause allowed only for UPDATE, DELETE.
Thanks for any help
October 10, 2008 at 10:06 am
So what is it you are actually trying to accomplish?
metalray (10/10/2008)
DECLARE @ColumnE As varchar(50)DECLARE product_cursor CURSOR FOR
SELECT F2 FROM dbo.Products
FOR UPDATE OF F2
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ColumnE
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @ColumnE =
--(SELECT F2
--FROM products
--WHERE CURRENT OF product_cursor
--)
UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor
FETCH NEXT FROM product_cursor INTO @ColumnE
END
CLOSE product_cursor
DEALLOCATE product_cursor
You don't need to try and SELECT a value into @ColumnE, the FETCH does that. I don't really understand your UPDATE statement, it will always be setting the value of column F2 to what it already is. Is there a real problem you were doing this test to try to accomplish? We might be better able to help you if we knew what you were trying to solve.
October 11, 2008 at 8:55 am
Well, as the error says, CURRENT OF is only allowed for updates and deletes. You use FETCH to get values into variables. Fetch isn't always next. You can get the values for the current cursor row using just FETCH
FETCH FROM contact_cursor INTO @LastName, @FirstName -- get values for current cursor row
Is this just for experimentation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2008 at 1:34 am
Hello Old Hand SSCertifiable,
yes, you are right, the
UPDATE dbo.Products SET F2 = @ColumnE WHERE CURRENT OF product_cursor
Is always setting the value of column F2 to what it already is.
So I need to specifiy the variable setting.
I think about something like that:
When:
ColumnE is not NULL then take that value
ColumnE is null instert the value taken
F2 looks like this:
[F2]
Car1
NULL
NULL
NULL
Car2
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Car3
NULL
Car4
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
and I want:
[F2]
Car1
Car1
Car1
Car1
Car2
Car2
Car2
Car2
Car2
Car2
Car2
Car2
Car3
Car3
Car4
Car4
Car4
Car4
Car4
Car4
Car4
Car4
Car4
October 12, 2008 at 2:18 am
How do you know which "CarN" apply to any particular row with NULL?
Just in case you don't know, there is no fixed order of rows in relational databases.
And you cannot rely on "physical order" of rows. It may be changed any moment just because of pages rearrangement or inserting a new row.
_____________
Code for TallyGenerator
October 12, 2008 at 8:09 am
Hi Hall of Fame,
well I have to rely on the order of rows. The data is imported from
an excel file.
I have to use the cursor that goes from top to bottom.
gets a non null value in to the variable and assigns this
variable to any column F2 until it hits a non-null value again.
I think the key is to get the variable to only take
a value when its not null and assign until it gets the next not null.
October 12, 2008 at 9:05 am
There's no such thing in SQL as 'Order of rows' unless you specify an order by. The order that the cursor returns them may change from one run to another.
How are you importing from Excel?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2008 at 12:59 pm
I am using a DTS package. well lets say I put an order by ID (which is from 1 to n ascending) in . that still leaves me with me issue of how to tell the variable what to take (not null) and when to set (null).
October 13, 2008 at 2:28 am
DECLARE product_cursor CURSOR FOR
SELECT F2 FROM dbo.Products ORDER BY tempID
OPEN product_cursor
--fetches to first row (which is NULL) so it should ideally not be stored in the variable @ColumnE
FETCH NEXT FROM product_cursor INTO @ColumnE
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH FROM product_cursor INTO @ColumnE WHERE product_curser IS NOT NULL
--I am trying to fetch only the NOT NULL rows into the variable @ColumnE
--PROBLEM!!!!!!!!!! a WHERE clause *conditional fetch* is not allowed at this point "Incorrect syntax --near the keyword 'WHERE'."
--fetches another row (which is probably NULL) so it should ideally not be stored in the variable @ColumnE
UPDATE dbo.Products SET F2 = @ColumnE WHERE F2 IS NULL
-- I am trying to set F2, in case it is NULL, to the current non-null value fetched into varialbe @ColumnE
END
--CLOSE product_cursor
--DEALLOCATE product_cursor
October 13, 2008 at 2:45 am
You don't need to do another fetch in the middle of the cursor loop. Fetch can't have a condition on it. It fetches values from a cursor row. All you can do is say this row, next row or a few other offset options
This will fix the cursor. I'm sure there's a solution that doesn't use cursors, I can't think of one off the top of my head though.
DECLARE @OldValue VARCHAR(10), @F2 VARCHAR(10), @ID int
DECLARE product_cursor CURSOR LOCAL STATIC READ_ONLY_FORWARD_ONLY FOR
SELECT tempID, F2 FROM dbo.Products ORDER BY tempID
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @id, @F2
SET @OldValue = @F2 -- assumes the first row will always have a value
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @F2 IS NULL
UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @id
ELSE
SET @OldValue = @F2
FETCH NEXT FROM product_cursor INTO @id, @F2
END
CLOSE product_cursor
DEALLOCATE product_cursor
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 2:58 am
Hi SSCertifiable,
thanks for the code.
THe issue is that somehow that my table looks like this:
[F2]
Car1
Car1
Car1
Car1
Car2
Car1
Car1
Car1
Car1
Car1
Car1
Car1
Car3
Car1
Car4
Car1
Car1
Car1
Car1
Car1
Car1
Car1
Car1
it seems like the cursor has fetched "Car1" and inserted it wherever there is a NULL
value without updating and using F2 if there is a non-null value such as Car2, Car3 or Car4.
You also wrote -- assumes the first row will always have a value, actually the first few rows (column F2)
are NULL (but I need them because the other columns have information in them).
October 13, 2008 at 3:10 am
that fixed it "AND @OldValue IS NOT NULL"
DECLARE @OldValue VARCHAR(50), @F2 VARCHAR(50), @ID int
DECLARE product_cursor CURSOR LOCAL
FOR
SELECT tempID, F2 FROM dbo.Products ORDER BY tempID
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ID, @F2
SET @OldValue = @F2
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @F2 IS NULL
UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @ID AND @OldValue IS NOT NULL
ELSE
SET @OldValue = @F2
FETCH NEXT FROM product_cursor INTO @ID, @F2
END
Thanks a lot !
October 13, 2008 at 3:13 am
Yes, your previous code, if you ran it would have done that, as your update statement was
UPDATE dbo.Products SET F2 = @ColumnE WHERE F2 IS NULL
If you ran that at all, then all of the table where F2 was null would have been updated with the first non-null value of @ColumnE. I've tested my code and it does what you want.
Can you reimport the table from Excel and start from scratch?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 3:21 am
Create table Products (
tempid int identity,
F2 varchar(10)
)
INSERT INTO Products (F2) VALUES ('Car1')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car2')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car3')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car4')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
select * from Products
DECLARE @OldValue VARCHAR(10), @F2 VARCHAR(10), @ID int
DECLARE product_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT tempID, F2 FROM dbo.Products ORDER BY tempID
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @id, @F2
SET @OldValue = @F2 -- assumes the first row will always have a value
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @F2 IS NULL
UPDATE dbo.Products SET F2 = @OldValue WHERE TempID = @id
ELSE
SET @OldValue = @F2
FETCH NEXT FROM product_cursor INTO @id, @F2
END
CLOSE product_cursor
DEALLOCATE product_cursor
-- drop table Products
Results:
tempidF2
1Car1
2Car1
3Car1
4Car1
5Car2
6Car2
7Car2
8Car2
9Car2
10Car2
11Car2
12Car2
13Car3
14Car3
15Car4
16Car4
17Car4
18Car4
19Car4
20Car4
21Car4
22Car4
23Car4
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 5:16 am
I wouldn't feel right if I didn't offer a non-cursor alternative.
Below should work fine for your scenario, but you may need to tweak your ProductId datatype in the @temp table and the @Prod Id variable.
DECLARE @temp TABLE
(
tmp_Id INT IDENTITY,
ProductId INT,
ColumnE VARCHAR(50)
)
INSERT INTO @temp
SELECT ProductId,F2 FROM Products
DECLARE @Count INT
DECLARE @Location INT
DECLARE @PrevF2 VARCHAR(50)
SELECT @Count = COUNT(1) FROM @temp
SET @Location = 1
WHILE @Location <= @Count
BEGIN
DECLARE @ColumnEVARCHAR(50)
DECLARE @ProdID INT
SELECT @ColumnE = ISNULL(ColumnE,@PrevF2), @ProdId = ProductId
FROM @temp
WHERE tmp_Id = @Location
UPDATE Products
SET F2 = @ColumnE
WHERE ProductId = @ProdId
SET @PrevF2 = @ColumnE
SET @Location = @Location + 1
END
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply