March 29, 2013 at 7:45 am
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.
March 29, 2013 at 7:58 am
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
March 29, 2013 at 8:03 am
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
March 29, 2013 at 8:10 am
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:
March 29, 2013 at 8:21 am
Thank you. I will definately read up on this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply