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 2008 Forums
 Transact-SQL (2008)
 Subquery Returned More than 1 value

Author  Topic 

deadtrees
Starting Member

26 Posts
Posted - 2011-12-28 : 13:07:24
Feel free to yell at me if my code is that bad, I'm new to this so advice is always welcome.

I have a query that is attempting to summarize info on People (Subjects) that have many decisions (Evaluations) associated to them. One of the things I'd like to do is summarize whether or not an examination of that person is complete. You can tell that an examination is incomplete by one of two variables. 1) the subject has a checkbox clicked stating they have no info on file(Subjects.NoInfo=1) or 2) one of the Evaluations is listed as Incomplete (EvidenceSubjects.Evaluation=4). I think the problem lies in the fact that these two variables exist in different tables.

So, I have a column [Exam Complete] that is utilizing a subquery to see if there's an Incomplete evaluations for a subject and utilizing a case statement based upon it finding the condition. If it does, it returns a N and if it's null it returns a different value. I'd like it to consider the second variable and thought it might be as easy as adding an OR clause because I've Set the Subquery's SubjectID to the Subject ID of the main query but that's when I get the error. It works fine when I take out the S2.NoInfo part, but obviously that's not a true reflection of the information in the case.


My question is: How do I make a multi conditional WHERE statement in a subquery that references the field I want in the main table? Or I'd also accept a 'Your repeated subquery structure between the two fields is a redundant newbie mistake and this is a better way of doing it' answer as well (problem code highlighted in red)

SELECT
	Upper(S2.SubjectLastName +' , '+S2.SubjectFirstName 	
	+' '+ISNULL(S2.SubjectMiddleName,' ')+' '+ ISNULL(S2.JS,' '))AS [NAME]	 
	,CONVERT(varchar(10),S2.DOB,101) AS [DOB]
	,S2.RequestNumber AS [REQUEST ID]
	,S2.AFISNumber AS [AFIS#],
	ISNULL((Case NoInfo
			WHEN 1
				THEN 'No Info on file'
			End), 'Subject on file') As [SUBJECT INFO],				
	ISNULL((SELECT (Case WHEN E2.SubjectID > 0 THEN 'Y' End)
			FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2 
			WHERE EXISTS(Select * 
						FROM LIMS_DATA_Production.dbo.EvidenceSubjects E1
						WHERE E1.Evaluation= 1 AND E1.ID = E2.ID And E2.SubjectID = S2.ID)),'N')As IDENTIFICATIONS,
	ISNULL((SELECT (Case WHEN E4.SubjectID > 0 THEN 'N' End)
			FROM LIMS_DATA_Production.dbo.EvidenceSubjects E4
			WHERE EXISTS(Select * 
						FROM LIMS_DATA_Production.dbo.EvidenceSubjects E3
						WHERE E3.ID = E4.ID And E3.SubjectID = S2.ID AND S2.NoInfo Is Not Null OR E3.Evaluation= 4)),'Y')As [EXAM COMPLETE]	  
FROM LIMS_DATA_Production.dbo.Subjects S2
WHERE  (S2.SDelete=0) AND (S2.CaseID=9307)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-28 : 13:31:27
make it like


SELECT
	Upper(S2.SubjectLastName +' , '+S2.SubjectFirstName 	
	+' '+ISNULL(S2.SubjectMiddleName,' ')+' '+ ISNULL(S2.JS,' '))AS [NAME]	 
	,CONVERT(varchar(10),S2.DOB,101) AS [DOB]
	,S2.RequestNumber AS [REQUEST ID]
	,S2.AFISNumber AS [AFIS#],
	ISNULL((Case NoInfo
			WHEN 1
				THEN 'No Info on file'
			End), 'Subject on file') As [SUBJECT INFO],				
	ISNULL(S3.IDENTIFICATIONS,'N')As IDENTIFICATIONS,
	ISNULL([EXAM COMPLETE],'Y')As [EXAM COMPLETE]	  
FROM LIMS_DATA_Production.dbo.Subjects S2
OUTER APPLY (SELECT MAX(Case WHEN Evaluation= 1 AND  E2.SubjectID > 0 THEN 'Y' End) AS IDENTIFICATIONS,
             MAX(Case WHEN (S2.NoInfo Is Not Null OR E3.Evaluation= 4) AND S2.NoInfo Is Not Null AND E2.SubjectID > 0 THEN 'Y' End) AS [EXAM COMPLETE]
	     FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2 
	     WHERE E2.SubjectID = S2.ID
            )S3
WHERE  (S2.SDelete=0) AND (S2.CaseID=9307)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

deadtrees
Starting Member

26 Posts
Posted - 2011-12-28 : 14:21:33
I changed the E3 reference to E2 in your code so it would run and then got this error.

Msg 8124, Level 16, State 1, Line 15
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-12-28 : 14:48:31
Visakh's query would certainly be more efficient, but I am unable to see the source that of error without having the benefit of some test data. Alternatively, you could also do the following(based on your original query).
SELECT
	Upper(S2.SubjectLastName +' , '+S2.SubjectFirstName 	
	+' '+ISNULL(S2.SubjectMiddleName,' ')+' '+ ISNULL(S2.JS,' '))AS [NAME]	 
	,CONVERT(varchar(10),S2.DOB,101) AS [DOB]
	,S2.RequestNumber AS [REQUEST ID]
	,S2.AFISNumber AS [AFIS#],
	ISNULL((Case NoInfo
			WHEN 1
				THEN 'No Info on file'
			End), 'Subject on file') As [SUBJECT INFO],				
	ISNULL((SELECT MAX((Case WHEN E2.SubjectID > 0 THEN 'Y' End))
			FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2 
			WHERE EXISTS(Select * 
						FROM LIMS_DATA_Production.dbo.EvidenceSubjects E1
						WHERE E1.Evaluation= 1 AND E1.ID = E2.ID And E2.SubjectID = S2.ID)),'N')As IDENTIFICATIONS,
	ISNULL((SELECT MAX((Case WHEN E4.SubjectID > 0 THEN 'N' End))
			FROM LIMS_DATA_Production.dbo.EvidenceSubjects E4
			WHERE EXISTS(Select * 
						FROM LIMS_DATA_Production.dbo.EvidenceSubjects E3
						WHERE E3.ID = E4.ID And E3.SubjectID = S2.ID AND S2.NoInfo Is Not Null OR E3.Evaluation= 4)),'Y')As [EXAM COMPLETE]	  
FROM LIMS_DATA_Production.dbo.Subjects S2
WHERE  (S2.SDelete=0) AND (S2.CaseID=9307)
Go to Top of Page

deadtrees
Starting Member

26 Posts
Posted - 2011-12-28 : 16:59:32
I see the concept, limit the row to one by instituting a MAX statement. And I think I see the concept of the Cross Apply to as a fancy way of outer joining a table function even though I don't fully understand the syntax.

The max statements allow it to run but doesn't return the correct results

Here's some data if you feel like taking it a step further

CREATE TABLE dbo.Subjects
(
[ID][int]NOT NULL PRIMARY KEY,
[CaseID][int]NULL,
[RequestNumber][int]NULL,
[DateAdded]	[datetime]NULL,
[SubjectAddedFrom]nvarchar(255)NULL,
[SubjectFirstName]nvarchar(255)NULL,
[SubjectLastName]nvarchar(255)NULL,
[SubjectMiddleName]nvarchar(255)NULL,
[JS]nvarchar(255)NULL,
[SVO]nvarchar(255)NULL,
[AFISNumber][int]NULL,
[DOB][datetime]NULL,
[SDelete]bit NULL,
[MorePrints]bit NULL,
[TimeConstraint] bit NULL,
[NoInfo]bit NULL
)
Go
INSERT INTO dbo.Subjects (ID, CaseID, RequestNumber, DateAdded, SubjectAddedFrom, SubjectFirstName, SubjectLastName, SubjectMiddleName, JS, SVO, AFISNumber, DOB, SDelete, MorePrints, TimeConstraint, NoInfo)
Values (6610,9307,5337,2/15/2011,AFISRun,JOHN,PUBLIC,Q,NULL,S,88888888,2/15/1900,0,NULL,NULL,NULL)
INSERT INTO dbo.Subjects (ID, CaseID, RequestNumber, DateAdded, SubjectAddedFrom, SubjectFirstName, SubjectLastName, SubjectMiddleName, JS, SVO, AFISNumber, DOB, SDelete, MorePrints, TimeConstraint, NoInfo)
Values (10142,9307,5765,4/15/2011,Request,JOHN,LAW,Q,NULL,O,99999999,4/15/1900,0,NULL,NULL,NULL)
INSERT INTO dbo.Subjects (ID, CaseID, RequestNumber, DateAdded, SubjectAddedFrom, SubjectFirstName, SubjectLastName, SubjectMiddleName, JS, SVO, AFISNumber, DOB, SDelete, MorePrints, TimeConstraint, NoInfo)
Values(10180,9307,5765,3/15/2011,Request,JANE,LAW,Q,NULL,S,77777777,3/15/1900,0,NULL,NULL,1)


CREATE TABLE dbo.EvidenceSubjects
(
[ID][int]NOT NULL PRIMARY KEY,
[SubjectID][int]NULL,
[EvidenceID][int]NULL,
[Evaluation][int]NULL,
[Required]nvarchar(255)NULL,
[IndTo]nvarchar(255)NULL,
[IndType]nvarchar(255)NULL,
[IndDate][datetime]NULL,
[ReqID][int]NULL,
[Comments]nvarchar(255)NULL,
)
Go
INSERT INTO dbo.EvidenceSubjects (ID, SubjectID, EvidenceID, Evaluation, Required, IndTo, IndType, IndDate, ReqID, Comments)
Values(28532,6610,39813,1,NULL,1,1,NULL,NULL,NULL)
INSERT INTO dbo.EvidenceSubjects (ID, SubjectID, EvidenceID, Evaluation, Required, IndTo, IndType, IndDate, ReqID, Comments)
Values(47372,6610,62196,2,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO dbo.EvidenceSubjects (ID, SubjectID, EvidenceID, Evaluation, Required, IndTo, IndType, IndDate, ReqID, Comments)
Values(47373,6610,62197,2,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO dbo.EvidenceSubjects (ID, SubjectID, EvidenceID, Evaluation, Required, IndTo, IndType, IndDate, ReqID, Comments)
Values(47371,10142,62196,2,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO dbo.EvidenceSubjects (ID, SubjectID, EvidenceID, Evaluation, Required, IndTo, IndType, IndDate, ReqID, Comments)
Values(47374,10142,62197,4,3,NULL,NULL,NULL,NULL,NULL)


You know you got the right answer when John Q Public is Exam Complete = Y because he has neither evaluation =4 Or Subjects.NoInfo=1 John Q Law has Exam Complete = N because He has 1 Evaluation =4 and Jane Q Law is Exam Complete N because she has Subjects.NoInfo=1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-29 : 00:20:30
[code]
SELECT
Upper(S2.SubjectLastName +' , '+S2.SubjectFirstName
+' '+ISNULL(S2.SubjectMiddleName,' ')+' '+ ISNULL(S2.JS,' '))AS [NAME]
,CONVERT(varchar(10),S2.DOB,101) AS [DOB]
,S2.RequestNumber AS [REQUEST ID]
,S2.AFISNumber AS [AFIS#],
ISNULL((Case NoInfo
WHEN 1
THEN 'No Info on file'
End), 'Subject on file') As [SUBJECT INFO],
ISNULL(S3.IDENTIFICATIONS,'N')As IDENTIFICATIONS,
CASE WHEN S2.NoInfo=1 OR [EXAMCnt] > 0 THEN 'N' ELSE 'Y' END As [EXAM COMPLETE]
FROM dbo.Subjects S2
OUTER APPLY (SELECT MAX(Case WHEN Evaluation= 1 AND E2.SubjectID > 0 THEN 'Y' End) AS IDENTIFICATIONS,
SUM(Case WHEN E2.Evaluation= 4 AND E2.SubjectID > 0 THEN 1 ELSE 0 End) AS [EXAMCnt]
FROM dbo.EvidenceSubjects E2
WHERE E2.SubjectID = S2.ID
)S3
WHERE (S2.SDelete=0) AND (S2.CaseID=9307)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

deadtrees
Starting Member

26 Posts
Posted - 2011-12-29 : 09:40:21
That did the trick! Thanks for showing me an alternative method which is much cleaner. I did get it to work as well by putting the E4=S2 join as well as the OR condition in the highest subquery as opposed to the lowest one.

Thanks again, I appreciate your help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site