Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Can I use SQL instead of cursor?

    Hi,

    I'm currently converting a VB function to SQL-Server. The function uses a cursor to find the "terms of delivery" (TOD) with the highest priority.

    I have a table with articlenumber, tod (and lots of other columns that doesn't matter now)

    ABC123 , AFG
    ABC123 , AFG
    ABC123 , BGH
    ABC123 , BGH
    ABC123 , CDD

    "CDD" has the highest priority and therefore ALL with the same articlenumber should use that tod.

    The existing function uses a cursor and loops through a recordset and updates every row with the same articlenumber as the current row with the tod with the highest priority (of the ones read) with the same articlenumber.

    One update per row takes "forever" to run...

    I figured it would be possible to select the tod with the highest priority for one articlenumber into a temp table and then do ONE update to set the tod on all rows...

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Here is a quick and dirty. It could be improved with a little work:



    create table foo (ArtNo varchar(25), pri varchar(25))
    insert into foo values ('ABC123' , 'AFG')
    insert into foo values ('ABC123' , 'AFG')
    insert into foo values ('ABC123' , 'BGH')
    insert into foo values ('ABC123' , 'BGH')
    insert into foo values ('ABC123' , 'CDD')
    insert into foo values ('ABCD123' , 'AFG')
    insert into foo values ('ABCD123' , 'AFG')
    insert into foo values ('ABCD123' , 'BGH')
    insert into foo values ('ABCD123' , 'BGH')
    insert into foo values ('ABCD123' , 'CDE')
    select ArtNo, max(pri) pri
    into #temp
    from foo
    group by ArtNo
    update foo
    set foo.pri = #temp.pri
    from foo,#temp
    where foo.Artno = #temp.ArtNo

    select * from foo
    drop table foo

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    Hmm..

    What if BGH has the highest priority?

    The priority is not determined by the alphabet. :-(

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Then you will need a table or some other method to determine an ordered set of priorities.

    As a start, look at how you do it now.

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    I'm at home now, but something struck me on the way home...

    If I change the three letter combinations to numbers according to their priority, I can then use:

    SELECT articlenumber, MAX(tod)
    FROM mytable
    GROUP BY articlenumber

    into a temptable..

    The I use the temptable and updates ALL articlenumbers with their right tod.

    I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)

  6. #6
    Join Date
    Jul 2003
    Posts
    123
    I'm at home now, but something struck me on the way home...

    If I change the three letter combinations to numbers according to their priority, I can then use:

    SELECT articlenumber, MAX(tod)
    FROM mytable
    GROUP BY articlenumber

    into a temptable..

    Then I use the temptable and updates ALL articlenumbers with their right tod.

    I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)

  7. #7
    Join Date
    Jul 2003
    Posts
    123
    I'm at home now, but something struck me on the way home...

    If I change the three letter combinations to numbers according to their priority, I can then use:

    SELECT articlenumber, MAX(tod)
    FROM mytable
    GROUP BY articlenumber

    into a temptable..

    Then I use the temptable and updates ALL articlenumbers with their right tod.

    I'll try it tomorrow. Clock is 5 in the afternoon in sweden now. :-)

  8. #8
    Join Date
    Jul 2003
    Posts
    123
    Worked like a charm.. My solution:

    ---

    /* Get priorities of tod */
    UPDATE mytable
    SET tod =
    CASE tod
    WHEN 'BGH' THEN '1'
    WHEN 'AFG' THEN '2'
    WHEN 'CDD' THEN '3'
    ELSE '999'
    END

    /* Create temptable */
    create table dbo.temp_table (articlenumber varchar(31), PRIO varchar(10))

    /* Make temporary list with "highest" TOD */
    insert into dbo.temp_table (articlenumber, PRIO)
    select articlenumber,
    CASE MIN(cast(tod AS INT))
    WHEN '1' THEN 'BGH'
    WHEN '2' THEN 'AFG'
    WHEN '3' THEN 'CDD'
    ELSE 'N/A'
    END
    from mytable
    GROUP BY articlenumber

    /* Update ALL to highest tod */
    UPDATE mytable
    SET tod = b.PRIO
    FROM mytable a, dbo.temp_table b
    WHERE
    (a.articlenumber = b.articlenumber)

    ---

Posting Permissions

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