February 8, 2008 at 1:08 pm
SELECT pidt.primary_id,
pit.prtype_id,
pit.prtype_name,
(SELECT @list_id = COALESCE(@list_id + ', ', '') + ' ' + LTRIM(RTRIM(pit1.secondary_id))
FROM partyInfoTable pit1
WHERE pit1.primary_id = pidt.primary_id)
AS list_id
FROM partyIDtable pidt
LEFT JOIN partyInfoTable pit
ON pit.primary_id = pidt.primary_id
Thanks for any assistance,
Ryan
February 8, 2008 at 1:13 pm
Did you declare the variable first?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 8, 2008 at 1:15 pm
Thanks for the reply,
Yeah, it's declared above it.
I only wanted to show the basic gist of the query and where the problem lies.
I can't understand why this is not working.
Ryan
February 8, 2008 at 1:27 pm
I would focus on the derived table:
(SELECT @list_id = COALESCE(@list_id + ', ', '') + ' ' + LTRIM(RTRIM(pit1.secondary_id))
FROM partyInfoTable pit1
WHERE pit1.primary_id = pidt.primary_id) as list_id
Pay close attention to the WHERE clause in particular. You're attempting to do a compare against a column in a table that is not listed in your FROM clause (pidt is not in your FROM clause).
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 8, 2008 at 1:27 pm
You don't have a comma after "pidt.primary_id" on the first line of the outer select.
If you get rid of the "@list_id = " part of the sub-query, it compiles. I've not used that syntax in a sub-query, so I'm not sure what it's meant to accomplish (having the variable in there).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2008 at 1:29 pm
Timothy Ford (2/8/2008)
I would focus on the derived table:(SELECT @list_id = COALESCE(@list_id + ', ', '') + ' ' + LTRIM(RTRIM(pit1.secondary_id))
FROM partyInfoTable pit1
WHERE pit1.primary_id = pidt.primary_id) as list_id
Pay close attention to the WHERE clause in particular. You're attempting to do a compare against a column in a table that is not listed in your FROM clause (pidt is not in your FROM clause).
The "pidt" in the sub-query is allowed because it references the outer select. It's called a "correlated sub-query".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2008 at 1:32 pm
Good Point GSquared. Thanks for the clarification. The syntax for this query is fairly wonky and I'm not used to coding in this manner on a regular basis.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 8, 2008 at 2:22 pm
Thanks for the replies,
Sorry about the comma not being in there, that was my doing while writing the query into this forum, but it's not that way in the actual running query.
The "variable = variable + ',blahblah' " is to concatenate the data into a comma-delimited list form.
So, If i removed the 'variable =' portion, it will not create a list, unfortunately.
This is what makes me wonder whether or not setting a variable within a sub-query is actually permitted or not.
This fashion works for regular queries, and therefore, i suspected that this would not be a problem in a subquery.
Is this actually the case?
Regards,
Ryan
February 8, 2008 at 2:30 pm
You might try changing it to a derived table in the From clause, instead of an inline sub-query in the Select clause. I haven't tested that, but from what you're saying, it might be worth a shot.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2008 at 2:41 pm
Your subquery can't be an update query (which it is with the @var=value statement in it)
A subquery has to actually return results. Again - having that assignment in there makes the whole thing an update query. You'd need something else to show you what you got out of the query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 2:55 pm
That being said - if I understand what you're trying to do - use the XML concatenation "trick".
It's faster and more effective than just about anything else out there. I'm stealing this code out of a good article about this from Jeff Moden from a few months ago, but here's an example.
SELECT t1.SomeID,
STUFF((SELECT ','+t2.SomeCode
FROM dbo.TestData t2
WHERE t1.SomeID = t2.SomeID FOR XML PATH('')),1,1,'') FROM dbo.TestData t1 GROUP BY t1.SomeID
Read the rest of it over here:
http://qa.sqlservercentral.com/articles/Test+Data/61572/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 3:06 pm
Thanks guys,
Your mentioning of this form of statement as a type of update query clarifies things quite easily.
Unfortunately, it pinholes my ability to simply get it to do what I need. This could be handled by an in-line function, however, after attempting this, I found out that the partyInfoTable table cannot be seen as it is created from a CTE, which the entire thing is already in a stored proc.
Maybe I'll have to break out the CTE into a separate stored proc to then create another stored proc for the remaining queries to see if the in-line function can 'see' the first stored proc table.
Do you think this would be a solution?
Regards,
Ryan
February 8, 2008 at 3:09 pm
Matt, I will definitely look at this... Thank you.
February 8, 2008 at 3:14 pm
Caffeine boost just kicked in. I think this is the XML trick applied to your case:
SELECT pidt.primary_id,
pit.prtype_id,
pit.prtype_name,
STUFF((SELECT ', ' + LTRIM(RTRIM(pit1.secondary_id))
FROM partyInfoTable pit1
WHERE pit1.primary_id = pidt.primary_id
FOR XML PATH('')),1,1,'')
AS list_id
FROM partyIDtable pidt
LEFT JOIN partyInfoTable pit
ON pit.primary_id = pidt.primary_id
GROUP BY pidt.primary_id,
pit.prtype_id,
pit.prtype_name
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 9:29 pm
Thanks, Matt!
The XML trick works perfectly, and no functions needed.
Your reference to the XML functionality expanded my SQL world as well.
This is a great tool to keep in my toolbox.
Thanks, again!
-Ryan
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply