Nested Select -- Results Set as varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested Select -- Results Set as varchar

Author

Topic

jone0497
Starting Member

8 Posts

Posted - 2007-09-24 : 14:43:28

Hello,

I am trying to get a result set into a varchar column. I am using M$ SQL Server 2000. This is what the intended table would look like:

RecurrenceTemp table
RecurID ActIDs
3 1,2,4,5,8
4 3,32,11,6
5 7,23,41

ActivitiesTemp table
ActID RecurID
1 3
2 3
3 4
4 3
5 3
6 4
7 5

My update query is:
UPDATE RecurrenceTemp
SET ActIDs = X.xActIDs
FROM
(
SELECT A.ActID,
(SELECT T.ActID FROM ActivitiesTemp AS T WHERE T.RecurID = A.RecurID) AS xActIDs,
A.RecurID AS xID
FROM
ActivitiesTemp AS A
) AS X
WHERE X.xID = RecurID;

It appears to hate the idea of my wanting it to return the result set of the (SELECT T.ActID FROM ActivitiesTemp AS T WHERE T.RecurID = A.RecurID) subquery as a column. It gives me the error: " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "

Anybody know how to fix it or a better way for me to be doing this?

Thanks,
JJ

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-24 : 16:01:37

What you need is a comma list first and then a loop construct. I don't have time to build it right now but I can point you to a tutorial that will help you do it.

http://www.4guysfromrolla.com/webtech/092105-1.shtml#postadlink

I'll check later and if nobody has updated it I will try and put one together.

Future guru in the making.

jone0497
Starting Member

8 Posts

Posted - 2007-09-24 : 16:39:04

Thanks for the link. This appears to give me the results that I wanted.

Function:
CREATE FUNCTION dbo.udf_GetActivities(@recurID int)
RETURNS VARCHAR(512) AS

BEGIN
DECLARE @ActivityList varchar(512)

SELECT @ActivityList = COALESCE(@ActivityList + ',', '') + CONVERT(VARCHAR(50),A.ActID)
FROM ActivitiesTemp A
WHERE A.recurID = @recurID

RETURN @ActivityList
END

Update query:
UPDATE RecurrenceTemp
SET ActIDs = X.xActIDs
FROM
(
SELECT A.ActID AS xActID,
dbo.udf_GetActivities(recurID) AS xActIDs,
A.RecurID AS xRecurID
FROM ActivitiesTemp AS A
WHERE A.RecurID > 0
) AS X
WHERE X.xRecurID = RecurID;

It appears to work fine.

Thanks,
JJ

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-24 : 18:48:51

quote:
Originally posted by jone0497

Thanks for the link. This appears to give me the results that I wanted.

Function:
CREATE FUNCTION dbo.udf_GetActivities(@recurID int)
RETURNS VARCHAR(512) AS

BEGIN
DECLARE @ActivityList varchar(512)

SELECT @ActivityList = COALESCE(@ActivityList + ',', '') + CONVERT(VARCHAR(50),A.ActID)
FROM ActivitiesTemp A
WHERE A.recurID = @recurID

RETURN @ActivityList
END

Update query:
UPDATE RecurrenceTemp
SET ActIDs = X.xActIDs
FROM
(
SELECT A.ActID AS xActID,
dbo.udf_GetActivities(recurID) AS xActIDs,
A.RecurID AS xRecurID
FROM ActivitiesTemp AS A
WHERE A.RecurID > 0
) AS X
WHERE X.xRecurID = RecurID;

It appears to work fine.

Thanks,
JJ

Awesome, glad I could help.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources