Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25

    Question Unanswered: Combining the results of a cursor loop

    Need a little help here.

    I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

    I need the results as one table.

    Here is my code.
    ___________________________________
    Code:
     SET NOCOUNT ON
    
    DECLARE @IdsString VARCHAR(255), @Id int
    
    
    SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
    
    DECLARE GetData CURSOR
    FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
    
    OPEN GetData
    
    FETCH NEXT FROM GetData
    INTO @Id
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	SELECT TOP 1 v.*
         	FROM dbo.GetProductRateView as v
    	WHERE v.[id] = @Id
    
    FETCH NEXT FROM GetData
    INTO @Id
    END
    
    CLOSE GetData
    DEALLOCATE GetData
    _____________________________________

    Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

    Any help would be much appreciated.

    NB Database was not designed and the client will not tolerate any changes to structure of the tables

    Regards

    Shaun McGuile
    Last edited by Shaun.McGuile; 08-07-07 at 07:11.

  2. #2
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25

    Lightbulb Here's my solution

    Code:
    SET NOCOUNT ON
    CREATE TABLE #CurrentRates
    (
    	AccountType VARCHAR(50),
    	EffectiveDate DATETIME,
    	tier INT,
    	gross FLOAT,
    	net FLOAT,
    	aer FLOAT,
    	footnotes VARCHAR(2000),
    	[id] INT
    )
    GO
    
    DECLARE @IdsString VARCHAR(255), @Id int
    
    
    SELECT @IdsString = '918|808|1214|89|995|300|526|1207'
    
    DECLARE GetData CURSOR
    FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s
    
    OPEN GetData
    
    FETCH NEXT FROM GetData
    INTO @Id
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	INSERT #CurrentRates  
    	SELECT TOP 1 v.*
         	FROM dbo.GetProductRateView as v
    	WHERE v.[id] = @Id
    
    FETCH NEXT FROM GetData
    INTO @Id
    END
    
    
    CLOSE GetData
    DEALLOCATE GetData
    
    SELECT * FROM #CurrentRates
    Works, but is it good?

    Regards

    Shaun McGuile

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dump the cursor and use a split function so you can do this in a set based fashion.
    http://www.sqlteam.com/forums/topic....48&whichpage=2
    See if you can get your string into a table of rows and then we can move on.

    BTW - have you changed your handle? What was it before?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Shaun.McGuile
    Works, but is it good?
    It uses cursors
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25

    Er.. they are Pootle

    Quote Originally Posted by pootle flump
    Dump the cursor and use a split function so you can do this in a set based fashion.
    http://www.sqlteam.com/forums/topic....48&whichpage=2
    See if you can get your string into a table of rows and then we can move on.

    BTW - have you changed your handle? What was it before?
    The line

    Code:
    ...Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s...
    Splits the ids into a table of column product id.

    Have I understood your question?

    Regards

  6. #6
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25

    Talking Handle?

    I have and always will be the one and only Shaun McGuile

    Lost my dbforums password/email combination somehow.

  7. #7
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25
    Forgot to add the 'drop table #CurrentRates' at the end of the code

    Doh!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - I thought you were parsing as a string.

    Ok - what's the pk of dbo.GetProductRateView?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25
    pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

    The db has no pk's nor integrity of any type its real bad

    dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

    Its like 'The Ring' of databases (like the film - you see it then you die) lol.

  10. #10
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25
    Well brace yourself

    Code:
    SELECT     TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate, 
                          dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer, 
                          dbo.saving_date.footnotes, dbo.saving_product.id
    FROM         dbo.saving_product INNER JOIN
                          dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
                          dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
    GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, 
                          dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
    ORDER BY MAX(dbo.saving_product_variant.date_id) DESC

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    date_id?

    Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

    From BoL:
    The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What version are you running BTW?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25

    Lightbulb So I change it to this..?

    Code:
    	INSERT #CurrentRates  
    	SELECT TOP 1 v.*
         	FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
    	WHERE v.[id] = @Id
    and remove the order by clause from the view?

    Regards

    Shaun McGuile

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can do - no need for the inner query BTW. I'm thinking more than that though.

    What version are you running?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2007
    Location
    Under a desk hiding from SSIS
    Posts
    25
    SQLServer 2000 is the db.

Posting Permissions

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