FOR XML PATH as Concatenation subquery and NULL

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)

FOR XML PATH as Concatenation subquery and NULL

Author

Topic

deadtrees
Starting Member

26 Posts

Posted - 2011-12-23 : 12:31:48

I'm pretty new to SQL having only really built queries in an access front end. I've run into something in SQL that's stumping me and hope you can help.

I'm concatenating many decisions(Evaluation) for one item(IMPRESSION) and using a Case statement. Sometimes the Case will result in a null because no decision was selected for that item but I'm Right Joining all the impressions.

My problem lies with the fact that I can't case select the null, I think it's because the null happens outside the case select subquery, but I don't know that for sure. I tried using explicit column name (case E2.Evaluation =) but that didn't work. I tried Case ISNULL(E2.Evaluation,'test') and case selected 'test', but that didn't work.

Can someone help?

SELECT  I."Lab Number" +' '+ I.LatentLetter +' '+I.ItemSource As [IMPRESSION],
	(SELECT (CASE E2.Evaluation
				WHEN 1
					THEN 'IND'
				WHEN 2
					THEN 'EXC'
				WHEN 3
					THEN 'INC'
				WHEN 4
					THEN 'ICP'
		 END)  + ' to '   ,S.SubjectLastName +' , '+S.SubjectFirstName +': '
 	FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2
		INNER 
		  JOIN LIMS_DATA_Production.dbo.Subjects S
	            ON E2.SubjectID = S.ID
	WHERE S.CaseID = I.CaseID AND S.SDelete = 0 AND E1.EvidenceID = E2.EvidenceID
	FOR XML PATH ('') ) AS RESULTS
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
RIGHT 
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
WHERE I.CaseID = 9307 and I.SDelete = 0
GROUP BY I.CaseID, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource
ORDER BY IMPRESSION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 13:09:15

put an ELSE condition inside CASE WHEN to avoid NULLs as it will return a value in all cases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deadtrees
Starting Member

26 Posts

Posted - 2011-12-23 : 16:00:48

I should have added, I tried that already, and it didn't work.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-23 : 17:09:17

I would debug this in two steps. First, remove the entire code for the column that has the subquery and run it.

SELECT  I."Lab Number" +' '+ I.LatentLetter +' '+I.ItemSource As [IMPRESSION],
	E1.EvidenceID as ARandomColumnFromEvidence
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
RIGHT 
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
WHERE I.CaseID = 9307 and I.SDelete = 0
GROUP BY I.CaseID, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource
ORDER BY IMPRESSION

Do you get at least one row from the IMPRESSIONS table for which there are no EvidenceSubjects? If you are not getting any rows, it is because of the where clause. Look at the where clause to understand why.

If that does not seem to be the problem, change the code as follows:

SELECT  I."Lab Number" +' '+ I.LatentLetter +' '+I.ItemSource As [IMPRESSION],ISNULL(
	(SELECT (CASE E2.Evaluation
				WHEN 1
					THEN 'IND'
				WHEN 2
					THEN 'EXC'
				WHEN 3
					THEN 'INC'
				WHEN 4
					THEN 'ICP'
		 END)  + ' to '   ,S.SubjectLastName +' , '+S.SubjectFirstName +': '
 	FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2
		INNER 
		  JOIN LIMS_DATA_Production.dbo.Subjects S
	            ON E2.SubjectID = S.ID
	WHERE S.CaseID = I.CaseID AND S.SDelete = 0 AND E1.EvidenceID = E2.EvidenceID
	FOR XML PATH ('') ),'No Evidence') AS RESULTS
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
RIGHT 
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
WHERE I.CaseID = 9307 and I.SDelete = 0
GROUP BY I.CaseID, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource
ORDER BY IMPRESSION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 23:41:36

its only CASE that returns NULL right? IF yes, wont this suffice?


SELECT  I."Lab Number" +' '+ I.LatentLetter +' '+I.ItemSource As [IMPRESSION],
	(SELECT COALESCE((CASE E2.Evaluation
				WHEN 1
					THEN 'IND'
				WHEN 2
					THEN 'EXC'
				WHEN 3
					THEN 'INC'
				WHEN 4
					THEN 'ICP'
		 END),'No Evidence')  + ' to '   ,S.SubjectLastName +' , '+S.SubjectFirstName +': '
 	FROM LIMS_DATA_Production.dbo.EvidenceSubjects E2
		INNER 
		  JOIN LIMS_DATA_Production.dbo.Subjects S
	            ON E2.SubjectID = S.ID
	WHERE S.CaseID = I.CaseID AND S.SDelete = 0 AND E1.EvidenceID = E2.EvidenceID
	FOR XML PATH ('') ) AS RESULTS
FROM  LIMS_DATA_Production.dbo.EvidenceSubjects E1
RIGHT 
  JOIN LIMS_DATA_Production.dbo.Impressions I
    ON E1.EvidenceID = I.ID  
WHERE I.CaseID = 9307 and I.SDelete = 0
GROUP BY I.CaseID, E1.EvidenceID, I."Lab Number", I.LatentLetter, I.ItemSource
ORDER BY IMPRESSION

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deadtrees
Starting Member

26 Posts

Posted - 2011-12-27 : 12:53:15

Sunitabeck, your suggestion (the second one utilizing ISNULL prior to the subquery) worked like a charm!

Thanks so much for the help, I truly appreciate it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-27 : 13:43:28

You are very welcome, DT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources