Cursor using DISTINC T - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor using DISTINC T

Author

Topic

jeantiger
Starting Member

5 Posts

Posted - 2007-07-31 : 10:05:44

hello

When i use the keyword DISTINC with a dynamique cursor , the results are static.When i remove DISTINC,everything is fine .

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

Note : i am not using the curosr to do an update , i am updating the data outside of it and iam using the cursor to fetch the new data but still the old data appears.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 10:30:34

1) WHERE CURRENT OF. Read about this in Books Online.

E 12°55'05.25"
N 56°04'39.16"

jeantiger
Starting Member

5 Posts

Posted - 2007-07-31 : 11:38:23

i tried to use CURRENT OF but didn't secceded , need some help.

first i created it :
DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

Then the update:

UPDATE am_location

SET location_en ='USA2'

WHERE CURRENT OF authors_cursor

and i got this error : The cursor is READ ONLY.
The statement has been terminated.

How can i remove the read only form a cursor ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 11:48:48

No cursor necessary at all

UPDATE  am_location
SET     location_en = 'USA2'
WHERE   LOCATION_ID =

E 12°55'05.25"
N 56°04'39.16"

jeantiger
Starting Member

5 Posts

Posted - 2007-07-31 : 11:57:47

so what Current of is used for?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources