Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Unanswered: SELECT DISTINCT will always result in a static cursor

    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 .

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Is this a less eloquent you?
    http://www.dbforums.com/showthread.php?t=1620891

    Anyway - have a think for 10 mins about why this would be the case. Seriously - there is an obvious reason - just see if you can crack it. If not I'll let you know
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    ok let me know

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh heh -exactly ten mins. I hope you tried

    Once you apply distinct you are no longer looking at the base table - you are looking at a resultset derived from the base table.
    SomeData SomeMoreData
    ----------------------------
    Poots Flump
    Poots Flump

    SELECT DISTINCT SomeData, SomeMoreData FROM MyTable

    returns

    SomeData SomeMoreData
    ----------------------------
    Poots Flump

    This is not a record in the table but a derived resultset so how can you update it? It cannot point to two rows in the table (as I presume you would like it to). That's why it cannot work.

    BTW - a cursor is rarely the correct choice for updating data in a table. Fancy telling us what you are trying to do?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2007
    Posts
    6
    Sorry , but 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.
    i don't don't know if u got what the situation is about .
    Anyway thanks for ur assistance

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - you are correct.

    I can't see why you are using a cursor here. How come not just a plain old DML statement?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2007
    Posts
    6
    The problem is, i have to use cursor cause there is a big application build on this kind of , thats why i need Cursors.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's no justification for cursors. I'll repeat Poots' question...
    Quote Originally Posted by pootle flump
    a cursor is rarely the correct choice for updating data in a table. Fancy telling us what you are trying to do?
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2007
    Posts
    6
    I'm using Cursor cause i have to display data in a Grid
    for example from row 100 to 120 how can i get result if i dont use cursor?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - so you are paging?
    What version of SQL Server are you using? (It is easier in 2005 you see).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2007
    Posts
    6
    sql server 2000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •