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.
___________________________________
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 :eek:

Regards

Shaun McGuileSET 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|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&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?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&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

...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|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||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.|||Well brace yourself

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|||date_id? :S

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.|||What version are you running BTW?|||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|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

collecting values into a variable in a loop

Dear All,

Im trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = @.myString + , + (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop

END

For some reason though the @.myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop
END

Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!Hi

Your variable is null initially -
NULL +'Something' = NULL

HTH|||Also - you don't need a loop:

DECLARE @.myString AS VarChar(1000)

SELECT @.myString = ''

SELECT @.myString = @.myString + ', ' + companyName
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 3, LEN(@.myString)-2)

SELECT @.myString|||This code will handle if any of the companyName is null.

DECLARE @.myString AS VarChar(1000)
SELECT @.myString = ''

SELECT @.myString = @.myString + coalesce(companyName+',','')
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 1, LEN(@.myString)-1)

SELECT @.myString|||Nice one thanks a lot! A final obsticle and I'm there!

As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

Char: CR, Oct:15, Dec:13, Hex:d

Any ideas anyone?|||Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!|||add char(13) with ur string.
eg:

PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'|||Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!