SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with Subquery in Select Statement


Problem with Subquery in Select Statement

Author
Message
shathaway 77080
shathaway 77080
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 29
This is my initial query that does work.

SELECT VW_ChildrenCurrentStatus.FosterChildID,
FosterChild.LegacyChildID,
Person.FirstName,
Person.LastName,
Person.MiddleInitial,
Person.DateOfBirth,
DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age,
VW_ChildrenCurrentStatus.StatusDate,
VW_ChildrenCurrentStatus.ProgramDescription,
VW_ChildrenCurrentStatus.HomeName,
(SELECT MAX(ActivityStartDate) AS MaxActivityStartDate
FROM ChildActivity
WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate,
VW_ChildrenCurrentStatus.HomeNumber,
VW_ChildrenCurrentStatus.LOCSetting,
VW_ChildrenCurrentStatus.FundingSource,
VW_ChildrenCurrentStatus.LOCCode,
VW_ChildrenCurrentStatus.LOCShortDesc,
AgencyCase.CaseName,
AgencyCase.WMSCaseName,
AgencyCase.CaseNamePersonID
FROM FosterChild LEFT OUTER JOIN
Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN
CaseChild LEFT OUTER JOIN
AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN
VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON
FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildID
WHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'
ORDER BY Person.LastName, Person.FirstName


Here is a second query that I would like to add as another sub query to add the address columns. By itself, it works.
The Address table can have more than one address for a personID, I need to return the fields associated with the most recent LastModified date.

SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted]
FROM [Options].[dbo].[Address] where PersonID =39785 order by LastModified desc

I have replaced the “39785” with the “AgencyCase.CaseNamePersonID”
This is the query I end up with.

SELECT VW_ChildrenCurrentStatus.FosterChildID,
FosterChild.LegacyChildID,
Person.FirstName,
Person.LastName,
Person.MiddleInitial,
Person.DateOfBirth,
DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age,
VW_ChildrenCurrentStatus.StatusDate,
VW_ChildrenCurrentStatus.ProgramDescription,
VW_ChildrenCurrentStatus.HomeName,
(SELECT MAX(ActivityStartDate) AS MaxActivityStartDate
FROM ChildActivity
WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate,
VW_ChildrenCurrentStatus.HomeNumber,
VW_ChildrenCurrentStatus.LOCSetting,
VW_ChildrenCurrentStatus.FundingSource,
VW_ChildrenCurrentStatus.LOCCode,
VW_ChildrenCurrentStatus.LOCShortDesc,
AgencyCase.CaseName,
AgencyCase.WMSCaseName,
AgencyCase.CaseNamePersonID,
(SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted]
FROM [Options].[dbo].[Address]
WHERE PersonID = AgencyCase.CaseNamePersonID
ORDER BY LastModified desc) as AddressColumns
FROM FosterChild LEFT OUTER JOIN
Person ON FosterChild.PersonID = Person.PersonID RIGHT OUTER JOIN
CaseChild LEFT OUTER JOIN
AgencyCase ON CaseChild.CaseID = AgencyCase.CaseID RIGHT OUTER JOIN
VW_ChildrenCurrentStatus ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID ON
FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildID
WHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'
ORDER BY Person.LastName, Person.FirstName


Error Message :

Msg 116, Level 16, State 1, Line 25
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Any help or ideas or different ways to accomplish the same thing would be greatly appreciated.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97207 Visits: 38988
Try this:



SELECT
VW_ChildrenCurrentStatus.FosterChildID,
FosterChild.LegacyChildID,
Person.FirstName,
Person.LastName,
Person.MiddleInitial,
Person.DateOfBirth,
DATEDIFF(yy,Person.DateOfBirth, GETDATE()) AS Age,
VW_ChildrenCurrentStatus.StatusDate,
VW_ChildrenCurrentStatus.ProgramDescription,
VW_ChildrenCurrentStatus.HomeName,
(SELECT MAX(ActivityStartDate) AS MaxActivityStartDate
FROM ChildActivity
WHERE (FosterChildID = VW_ChildrenCurrentStatus.FosterChildID) AND (ActivityTypeID IN (3, 4, 18))) AS placementdate,
VW_ChildrenCurrentStatus.HomeNumber,
VW_ChildrenCurrentStatus.LOCSetting,
VW_ChildrenCurrentStatus.FundingSource,
VW_ChildrenCurrentStatus.LOCCode,
VW_ChildrenCurrentStatus.LOCShortDesc,
AgencyCase.CaseName,
AgencyCase.WMSCaseName,
AgencyCase.CaseNamePersonID,
ds.[Address1],
ds.[Address2],
ds.[City],
ds.[StateID],
ds.[Zip],
ds.[Created],
ds.[LastModified],
ds.[FlagDeleted]
FROM
FosterChild
LEFT OUTER JOIN Person
ON FosterChild.PersonID = Person.PersonID
RIGHT OUTER JOIN CaseChild
LEFT OUTER JOIN AgencyCase
ON CaseChild.CaseID = AgencyCase.CaseID
RIGHT OUTER JOIN VW_ChildrenCurrentStatus
ON CaseChild.ChildID = VW_ChildrenCurrentStatus.FosterChildID
ON FosterChild.FosterChildID = VW_ChildrenCurrentStatus.FosterChildID
OUTER APPLY (SELECT top 1 [Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted]
FROM [Options].[dbo].[Address]
WHERE PersonID = AgencyCase.CaseNamePersonID
ORDER BY LastModified desc) ds([Address1],[Address2],[City],[StateID],[Zip],[Created],[LastModified],[FlagDeleted])
WHERE VW_ChildrenCurrentStatus.STATUS_TYPE = 'Active'
ORDER BY Person.LastName, Person.FirstName





Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
shathaway 77080
shathaway 77080
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 29
That worked beautifully - Thank you.

Now let me see if I understand what you did.

Is the Outer Apply creating a temp table called ds?

Thank you again,

Steve
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97207 Visits: 38988
shathaway 77080 (3/29/2013)
That worked beautifully - Thank you.

Now let me see if I understand what you did.

Is the Outer Apply creating a temp table called ds?

Thank you again,

Steve



The query associated with the OUTER APPLY is a derived table. The OUTER APPLY works like an OUTER JOIN, if there is no information returned in the subquery, it returns nulls. IF you change the OUTER APPLY to a CROSS APPLY it works like an INNER JOION and if there is nothing returned you lose those rows in the query. Since you are using numerous LEFT and RIGHT outer joins I thought the OUTER APPLY made better sense in this query.

Please take the time to read the following articles to help learn more about APPLY:

http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
shathaway 77080
shathaway 77080
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 29
Thank you. I will definately read up on this.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search