Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Subquery in Select Statement Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 9:15 AM
Points: 19, Visits: 25


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.
Post #1436930
Posted Friday, March 29, 2013 7:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,083, Visits: 31,624
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






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)
Post #1436939
Posted Friday, March 29, 2013 8:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 9:15 AM
Points: 19, Visits: 25
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

Post #1436942
Posted Friday, March 29, 2013 8:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,083, Visits: 31,624
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/




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)
Post #1436950
Posted Friday, March 29, 2013 8:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 26, 2013 9:15 AM
Points: 19, Visits: 25
Thank you. I will definately read up on this.
Post #1436958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse