Help With SQL Query Error

[penfold_99]

Hi All,

 

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

 

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
                         (SELECT     email_address
                           FROM          sims.rpt_vix_StudentContact_705
                           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

 

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.

 

Any one got an idea?

[FABEnterprises]

Can you explain what data/result you are trying to get with the replace function? Also why are you using FOR XML statement? Could that be the problem?

 

Mason

[penfold_99]

Can you explain what data/result you are trying to get with the replace function? Also why are you using FOR XML statement? Could that be the problem?

 

Mason

 

what i am trying to to is turn multiple rows in to a single row.

 

the data is the follow

 

contact_id|forename|surname|student count|email|role|UPN

 

1|joe|bloggs|2|[email protected]|parent|1234ACBD

1|joe|bloggs|2|[email protected]|parent|5678ABCD

 

needs to be converted to the following

 

1|joe|bloggs|2|[email protected]|parent|1234ACBD;5678ABCD

[lightinthedark]

@FABEnterprises: In this context "FOR XML PATH" is being used to compensate for MSSQL not having a "GROUP_CONCAT" function like MySQL does which allows for multiple rows to be combined into a single column on a single row. It takes the data from the SELECT inside the REPLACE and puts it all as one line of text which REPLACE then formats a bit more nicely ... in theory. Pretty clever way of bypassing some of MSSQL's limitations if you ask me.

 

@Penfold:

As I'm sure you're aware you shouldn't be poking the SIMS database. Naughty naughty.

As for the query, it looks ok to me (though I'd have indented it differently :-P ) . and I see no reason why it shouldn't run fine. Really. I expect that if I ran this exact query on our server it'd come up with half a dozen nicely formated rows. Have you tried running just the subquery in the REPLACE on its own? Building up the whole query bit by bit? If you make any progress I'd be very interested to know how you get on.

 

Another alternative is to use the commandreporter tool to grab all the relevant data to a csv or some such then import it into a database that allows you to do useful things like GROUP_CONCAT ... such as MySQL. Sorry it's not a more helpful answer.

 

[edit]

Looking at the reporting options within SIMS, it seems entirely possible to get the multi-row version of what you are after by using subreports and the like. Is it too much hardship to manually copy the UPNs for a contact's multiple children into a single entry/cell/whatever ? I know it's a manual process and therefore sucks, but it could be a heck of a lot easier in the short term. I guess the question is: how often are you going to need to do this?

[/edit]

 

[edit2]

Having just run a report that I think gets the data you're after (but on multiple lines per contact) I get quite a few results, so maybe a manual process isn't a good fit. If you export the results of such a report to csv or xml then I'm sure you could use any number of linux-based text editing tools (sed, awk, etc.) to post-process the result list. Heck, I'm pretty sure that Excel has some sort of mechanism to do this (pivot tables?) if you get the data in there.

Basically I'm saying that this may be a cat better skinned outside of MSSQL.

[/edit2]

Edited May 13, 2009 by lightinthedark
afterthought(s)

[penfold_99]

@Penfold:

As I'm sure you're aware you shouldn't be poking the SIMS database. Naughty naughty.

 

I am allowed to poke about with the DB directly as there is a caveat that if i break it i will pay for it to be fixed. So far, no breakages as all im doing is read only. If SIMS.net Business Objects Documentation was a little bit clearer and had a few more examples i would use it.

 

[edit2]

Having just run a report that I think gets the data you're after (but on multiple lines per contact) I get quite a few results, so maybe a manual process isn't a good fit. If you export the results of such a report to csv or xml then I'm sure you could use any number of linux-based text editing tools (sed, awk, etc.) to post-process the result list. Heck, I'm pretty sure that Excel has some sort of mechanism to do this (pivot tables?) if you get the data in there.

Basically I'm saying that this may be a cat better skinned outside of MSSQL.

[/edit2]

 

I'm looking at parental access for moodle and this query is the data source. I'm trying to get a fully automated solution due to the sheer about of records and rolle assignments that need to take place.

 

FreeTDS errors and moodle takes it as can't connect, i would like to surpress this error if possible, if the query can't be fixed i will have to do the heavy lifting in php.

[FABEnterprises]

what i am trying to to is turn multiple rows in to a single row.

 

the data is the follow

 

contact_id|forename|surname|student count|email|role|UPN

 

1|joe|bloggs|2|[email protected]|parent|1234ACBD

1|joe|bloggs|2|[email protected]|parent|5678ABCD

 

needs to be converted to the following

 

1|joe|bloggs|2|[email protected]|parent|1234ACBD;5678ABCD

 

Thanks that's much clearer.

 

Pity you are doing this directly with SIMs as it limits your options as you could have created a UDF to replace your REPLACE section and put something like this:

 

--Function with @Contact_ID param--

DECLARE @MyString varchar(max)

SELECT @MyString = COALESCE(@MyString +';','') + unique_pupil_no FROM MyStudentBrowseContactQuery WHERE Contact_ID = @Contact_ID

Return @MyString

--end function--

 

Perhaps you could have a look at the PIVOT option in MSSQL not sure you can concatenate a pivoted column, worth a look.

[Pashers]

A query walks into a resturaunt, walks over to two tables and says "excuse me, do you mind if I join you?"