Help With SQL Query Error
I am getting the following error
Msg 8153, Level 0, State 1, Server XXXX\XXXX, Line 2
Warning: Null value is eliminated by an aggregate or other SET operation.
The query i'm running is
I problem is the select within the replace as it works find without it (but doesn't include the data i need) I have tried check everything is not null but still no luck.
SELECT contact_id, forename, surname, COUNT(contact_id) AS students, email_address AS email, 'Parent' AS role, UPNS = REPLACE
((SELECT unique_pupil_no AS [data()]
FROM sims.stud_via_student_browse INNER JOIN
sims.rpt_vix_StudentContact_705 ON sims.stud_via_student_browse.person_id = sims.rpt_vix_StudentContact_705.student_id
WHERE sims.rpt_vix_StudentContact_705.contact_id = c.contact_id AND sims.rpt_vix_StudentContact_705.email_address IS NOT NULL AND
sims.rpt_vix_StudentContact_705.forename IS NOT NULL AND unique_pupil_no IS NOT NULL
ORDER BY sims.rpt_vix_StudentContact_705.contact_id FOR XML PATH('')), ' ', ';')
FROM sims.rpt_vix_StudentContact_705 AS c
WHERE (email_address IN
GROUP BY email_address
HAVING (COUNT(contact_id) > 1))) AND (forename IS NOT NULL) AND (parent = 'T') AND (email_address IS NOT NULL) AND student_id IS NOT NULL
GROUP BY email_address, surname, contact_id, forename
Any one got an idea?