Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: Loop thru data subset

    hi

    I have the following query:
    select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid
    from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
    where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid

    This returns multiple rows of data. I want to loop through each row, extract 2 values and see if they exist in another table. I need to do this in Query Analyser.

    Can anyone please help!

    Thanking you in advance
    PORRASTAR

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Of course you can use cursor, but I prefere to use fake cursor:

    Save your result in temporary table and do loop by newid.

    select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid,
    IDENTITY(int, 1, 1) AS newid -- add identity filed
    into #tmp -- temp table
    from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
    where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think you need to loop through anything if you just want to see if the values exist in another table. Just treat your query as a subquery and join it to your other table:

    select Value1, Value2
    from YourOtherTable
    inner join
    (select sum(iunits) as iunits, sum(ivalue) as ivalue, sum(ivolume) as ivolume, sum(ivolumeHL) as ivolumehl, periodid as periodid, productid as productid, tbstores.storeid as storeid
    from tbstoredata
    inner join tbstores on tbstoredata.storeid = tbstores.storeid
    where tbstoredata.uploadid = 111
    group by tbstores.storeid, periodid, parentid, productid) SummarySubquery
    on YourOtherTable.Value1 = SummarySubquery.Value1 and YourOtherTable.Value2 = SummarySubquery.Value2

    ...or if you want to use the dataset multiple times, store it in a temporary table or table variable and join that instead.

    blindman

Posting Permissions

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