+ Post New Thread
Results 1 to 7 of 7
Coding Thread, Help With SQL Query Error in Coding and Web Development; Hi All, I am getting the following error Msg 8153, Level 0, State 1, Server XXXX\XXXX, Line 2 Warning: Null ...
  1. #1
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    929
    Thank Post
    56
    Thanked 162 Times in 114 Posts
    Rep Power
    67

    Help With SQL Query Error

    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

    Code:
    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?

  2. #2
    FABEnterprises's Avatar
    Join Date
    Apr 2009
    Location
    Hastings
    Posts
    12
    Thank Post
    0
    Thanked 4 Times in 3 Posts
    Rep Power
    11
    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

  3. #3
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    929
    Thank Post
    56
    Thanked 162 Times in 114 Posts
    Rep Power
    67
    Quote Originally Posted by FABEnterprises View Post
    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|p@p.com|parent|1234ACBD
    1|joe|bloggs|2|p@p.com|parent|5678ABCD

    needs to be converted to the following

    1|joe|bloggs|2|p@p.com|parent|1234ACBD;5678ABCD

  4. #4

    Join Date
    Oct 2008
    Location
    Gosport
    Posts
    64
    Thank Post
    1
    Thanked 18 Times in 13 Posts
    Rep Power
    15
    @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]
    Last edited by lightinthedark; 13th May 2009 at 11:17 PM. Reason: afterthought(s)

  5. #5
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    929
    Thank Post
    56
    Thanked 162 Times in 114 Posts
    Rep Power
    67
    Quote Originally Posted by lightinthedark View Post
    @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.

    Quote Originally Posted by lightinthedark View Post
    [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.

  6. #6
    FABEnterprises's Avatar
    Join Date
    Apr 2009
    Location
    Hastings
    Posts
    12
    Thank Post
    0
    Thanked 4 Times in 3 Posts
    Rep Power
    11
    Quote Originally Posted by penfold_99 View Post
    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|p@p.com|parent|1234ACBD
    1|joe|bloggs|2|p@p.com|parent|5678ABCD

    needs to be converted to the following

    1|joe|bloggs|2|p@p.com|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.

  7. #7

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    292
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    18
    A query walks into a resturaunt, walks over to two tables and says "excuse me, do you mind if I join you?"


SHARE:
+ Post New Thread

Similar Threads

  1. WSS 3.0 SQL Query
    By phreak in forum Windows
    Replies: 1
    Last Post: 28th April 2009, 01:06 PM
  2. SQL update query
    By MACIT in forum Coding
    Replies: 4
    Last Post: 27th October 2008, 08:20 PM
  3. SQL Query Using Full Text Engine inSQL 2005
    By Phanikumar in forum Web Development
    Replies: 0
    Last Post: 22nd September 2008, 08:47 AM
  4. SQL Insert Query
    By Pashers in forum Web Development
    Replies: 1
    Last Post: 9th September 2008, 03:42 PM
  5. SQL Query :S
    By Kained in forum Web Development
    Replies: 10
    Last Post: 25th July 2007, 02:56 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •