Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    30

    Unanswered: How to write SQL query and not cursor ?

    Please help me to get the required result:
    For each IDS in table1
    - change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3)
    - in table2, find the maxVal for each number
    - disply the table1..ids, number, table2..maxVal & table1..idsDesc,
    order by table1..ids, table2..maxVal & IntValue

    I have 2 tables, over milin records each. The Simplified versions of the tables looks like that:

    create table table1 (ids varchar(100), idsDesc varchar(100))
    go
    insert table1 select '1,2,3', 'Description 1'
    union all select '2,3,4', 'Description 2'
    union all select '1,7', 'Description 3'
    union all select '16,3,8', 'Description 4'
    union all select '2,5,6,1', 'Description 5'
    go

    create table table2 (ids int, maxVal int)
    go
    insert table2 select 1, 10
    union all select 2, 6
    union all select 3, 12
    union all select 4, 11
    union all select 5, 66
    union all select 6, 4
    union all select 7, 3
    -- union all select 8, 5 -- no value for 8
    union all select 9, 6
    union all select 16, 12
    go

    I have also function that returns table variable of numbers delivered from given string:
    create function dbo.fn_StrToIntValues ( @str varchar(1000) )
    returns @numsTbl table (IntValue int not null)

    The command
    select * from dbo.fn_StrToIntValues('1,2,33')

    Returns --> intValue
    1
    2
    33

    Can I use SQL query and not cursor to get the following result ?

    Required Output :
    ids IntValue maxVal idsDesc
    --------- -------- ------- ---------------
    '2,5,6,1' 5 66 'Description 5'
    '16,3,8' 16 12 'Description 4'
    '2,3,4,9' 3 12 'Description 2'
    '1,2,3' 3 12 'Description 1'
    '1,7' 1 10 'Description 3'

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Can you use the function to select from and hook it up to table2 ?

  3. #3
    Join Date
    Sep 2004
    Posts
    30
    Thanks for the replay.

    My sql look like this:

    declare @ids varchar(100)
    declare @tmpIds table (ids varchar(100), singleId int, idCount int)

    DECLARE c_tel CURSOR FOR SELECT DISTINCT ids FROM table1
    OPEN c_tel

    FETCH NEXT FROM c_tel INTO @ids
    WHILE @@FETCH_STATUS = 0
    BEGIN
    insert into @tmpIds (ids, singleId, idCount)
    select top 1 allIds, intValue, isnull(maxVal,0) as maxVal
    from (select @ids as allIds, intValue, maxVal
    from dbo.fn_ParseStringToIntValues(@ids)
    left outer join table2
    on intValue = ids) as a
    order by maxVal desc


    FETCH NEXT FROM c_tel INTO @ids
    END
    CLOSE c_tel
    DEALLOCATE c_tel

    select t.ids, t.singleId, t.idCount,d.idsDesc
    from @tmpIds t inner join table1 d on d.ids = t.ids
    order by t.idCount desc, t.ids

    Can I rewrite the program to get the same output without using cursor ?

    Thanks
    Helena

Posting Permissions

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