Subquery Error

Hello
I'm returning to SQL after a number of years and struggling tbh. I'm not entirely sure I will be making sense. This is probably quite simple. This code creates XML that is pulled into another system and i've been asked to edit to incorporate attendance but it has to be a subquery as it imports more than one field.

The specific error is on this part

( SELECT
sd.PossibleMarks
,sd.PresentMarks
FROM sd) AS Attendance

Invalid object name 'sd'.

This is the whole query. Could someone help set me straight please?

DECLARE @academicYearID varchar(20)
SET @academicYearID = '20/21'

SELECT

	sd.studentid as ID
	,LTRIM(RTRIM(sd.FirstForename)) as Forename
	,LTRIM(RTRIM(sd.Surname)) as Surname
	,sd.FirstForename as LegalForename
	,LTRIM(RTRIM(sd.Surname)) as LegalSurname
	,CASE sd.Sex WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ELSE '' END as Gender
	,sd.UniqueLearnerNo as ULN
	,convert(varchar(10), sd.DateOfBirth, 120) as DateOfBirth
	,replace(replace(replace(sd.Address1+'|'+isnull(sd.Address2,'')+'|'+
	isnull(sd.Address3,'')+'|'+isnull(sd.Address4,'')+'|'+
	ltrim(rtrim(sd.PostcodeOut))+' '+ltrim(rtrim(sd.PostcodeIn)),'|','<>'),'><',''),'<>',CHAR(13)+CHAR(10)) as AddressBlock
	,LTRIM(RTRIM(coalesce(sd.MobileTel,sd.Tel1,''))) as Telephone
	,coalesce(LTRIM(RTRIM(sd.Email)),'') as Email
	,sd.EthnicGroup AS Ethnicity
	
	,( SELECT
	sd.PossibleMarks
	,sd.PresentMarks
	FROM sd) AS Attendance
	
	
	,(
	select
	sn.NeedType
	,sn.NeedTypeCode
	from
	(
	select
	sd1.StudentDetailID
	,ld.Description as NeedType
	,'L'+CONVERT(varchar(10),ld.LearningDifficultyID) as NeedTypeCode
	from ProSolution..StudentDetail sd1 with (nolock)
	join ProSolution..LearningDifficulty ld with (nolock)
	on sd1.LearningDiffOrDisID=ld.LearningDifficultyID 
	where ld.LearningDifficultyID =1
	UNION
	select
	sd2.StudentDetailID
	,d.Description as NeedType
	,'D'+Convert(varchar(10),d.DisabilityCategoryID) as NeedTypeCode

	from ProSolution..StudentDisability sd2 with (nolock)
	join ProSolution..DisabilityCategory d with (nolock)
	on sd2.DisabilityCategoryID=d.DisabilityCategoryID
	where d.DisabilityCategoryID < 98
	) sn
	where sd.StudentDetailID=sn.StudentDetailID
	for xml path('SpecialNeed'), type, elements
	) 
	from 
	(
	select
	distinct
	sd.studentid
	,sd.StudentDetailID
	,sd.FirstForename
	,sd.Surname
	,sd.sex
	,sd.DateOfBirth
	,sd.Address1
	,sd.Address2
	,sd.Address3
	,sd.Address4
	,sd.PostcodeOut
	,sd.PostcodeIn
	,sd.UniqueLearnerNo
	,sd.MobileTel
	,sd.Email
	,sd.Tel1
	,sd.EthnicGroup
	,sd.PossibleMarks
	,sd.PresentMarks

	from ProSolutionReports.dbo.OurStudentDetailCPOMS sd
	join ProSolution..Enrolment e with (nolock)
	on sd.StudentDetailID=e.StudentDetailID
	--and e.UserDefined13=1
	where sd.AcademicYearID=@academicyearid

-- and ProSolution.dbo.GetAgeOn31Aug(sd.dateofbirth,sd.academicyearID) < 25
--and e.CompletionStatusID='1'
) sd
order by sd.StudentID

Thankyou for any asistance

SD doesn't exist, it's an alias for ProSolutionReports.dbo.OurStudentDetailCPOMS. You are also selecting those columns, so you'll have to remove and use stuff. Something like

STUFF(
( SELECT '-' + sd.PossibleMarks + ',' + sd.PresentMarks
FROM ProSolutionReports.dbo.OurStudentDetailCPOMS sd1
WHERE sd1 .[StudentDetailID]=sd1 .[StudentDetailID]
FOR XML PATH('')
),
1,
1,''
) AS t

If you provide DDL and Sample data, we could better help you

1 Like

If you are looking at adding additional columns from the table OurStudentDetailCPOMS then all you need to do is:

       , sd.PossibleMarks
       , sd.PresentMarks

No need for a sub-query to pull those columns since you already have access to them from the table. If those columns are coming from a different table - then you need to join to that table and then reference the columns from that table.

Finally - if the columns are coming from a different table and there are multiple rows per student you may need an outer/cross apply to get the 'latest' rows for the student.

You also have a problem with this query - using the same alias for the derived table and the actual table is going to cause confusion, since you now need to include additional columns in the derived table as 'sd.Something' and then reference that column from the derived table as 'sd.Something'.

Because you are using DISTINCT - I am assuming that there are multiple rows being returned in your derived table, which is most likely due to either a bad join or not considering all of the required columns in the where clause.

1 Like

Thankyou. By sample data do you mean the sample xml output?

Create some sample data as a create table (or declare a table variable) - provide the sample data with insert statements to your temp table, and the expected results. For example:

Create Table #myTempTable (col1 varchar(20), col2 varchar(30), ....);
Insert Into #myTempTable
Values ('one', 'two', ...)
     , ('three', 'four', ...);

And - the expected output using that data.

1 Like

All I'm working from is an expected XML output and trying to get back to the SQL to replicate it. Its just down to the Attendance block now. Everything else is working. I had it as you said like this
, sd.PossibleMarks
, sd.PresentMarks

I created those two fields in a different view in order to pull them in but it turns out its not the correct way they need in order for it to feed to the XML

They've said its not matching the template, there needs to be an attendance block like the Special Need block (which is a subquery) hence why I then tried to make a subquery called attendance with those two fields in

I probably need to go away and look at it when I've not bene looking at it all day

This is the sub-query you have for the needs type (reformatted):

( Select sn.NeedType
       , sn.NeedTypeCode
    From ( Select sd1.StudentDetailID
                , ld.Description                                       As NeedType
                , 'L' + convert(varchar(10), ld.LearningDifficultyID)  As NeedTypeCode
             From ProSolution..StudentDetail sd1 With (nolock)
             Join ProSolution..LearningDifficulty ld With (nolock) On sd1.LearningDiffOrDisID = ld.LearningDifficultyID
            Where ld.LearningDifficultyID = 1
          Union
          Select sd2.StudentDetailID
                , d.Description                                       As NeedType
                , 'D' + convert(varchar(10), d.DisabilityCategoryID)  As NeedTypeCode
             From ProSolution..StudentDisability sd2 With (nolock)
             Join ProSolution..DisabilityCategory d With (nolock) On sd2.DisabilityCategoryID = d.DisabilityCategoryID
            Where d.DisabilityCategoryID < 98
         ) sn
   Where sd.StudentDetailID = sn.StudentDetailID
         For xml Path('SpecialNeed'), Type, elements)

This sub-query is returning an XML node/path with the name 'SpecialNeed' and embedding that in the overall results. However, I am not seeing the overall query returning XML data...

So what you need is another sub-query to return a new node/path with the name specified as Attendance - pulling the data from the correct tables.

, ( Select sa.PossibleMarks
       , sa.PresentMarks
    From ( Select a.StudentDetailID
                , a.PossibleMarks
                , a.PresentMarks
             From ProSolution..StudentAttendance                a 
         ) As sa
   Where sa.StudentDetailID = sd.StudentDetailID
         For xml Path('Attendance'), Type, elements)

Not sure what table to pull from - or how you get the attendance marks...

Once you have that, then you need to include the FOR XML at the end to define the rest of the data as XML.

1 Like

This is ringing a bell now and it’s very useful. I’ll revisit tomorrow and report back.

Many thanks for your guidance.

So this process produces an actual file and saved to disk?

I've got it to run and match the XML template and with the correct output so thankyou very much for your time taken to help. I had to create a new attendance view then reference it in the new sub query

Thanks again, should be a big help in future.