June 12, 2012 at 5:31 pm
Hi All,
It's been a while since I have posted in here 🙂
I am trying to update my query to add a count of Student_ID's (minAltID) and need the Group By to do perfom the count.
Why will SQL Server not let me use minAltID in my Group By?
SELECT COALESCE((SELECT TOP 1 Tbl_Student_ID_XRef.Alt_ID FROM Tbl_Student_ID_XRef
INNER JOIN Tbl_Location_Control ON Tbl_Student_ID_XRef.Format_ID = Tbl_Location_Control.Primary_ID
WHERE Tbl_Student_ID_Xref.Student_ID = Tbl_Student_Master.student_ID and Tbl_Location_Control.location_id = Tbl_eAdvising.location_ID),
(SELECT min(Tbl_Student_ID_XRef.Alt_ID) FROM Tbl_Student_ID_XRef WHERE Tbl_Student_ID_Xref.Student_ID = Tbl_eAdvising.student_ID)) AS minAltID,
Tbl_Student_Master.Full_Name, Tbl_Counselor_Master.Short_Name, Tbl_eAdvising.Student_Submitted, Tbl_eAdvising.Viewed_By_Advisor,
Tbl_eAdvising.Advisor_Responded, Tbl_eAdvising.Student_Viewed_Response, Tbl_Location_Control.Location_Code, Tbl_eAdvising_Questions.Subject,
Tbl_eAdvising_Questions.Body, Tbl_eAdvising_Questions.Response, Tbl_eAdvising.Response_Satisfactory, Tbl_Counselor_Location_XRef.Active
From Tbl_eAdvising
INNER JOIN Tbl_Student_Master ON Tbl_eAdvising.Student_ID = Tbl_Student_Master.Student_ID
INNER JOIN Tbl_eAdvising_Questions ON Tbl_eAdvising.eAdvising_ID = Tbl_eAdvising_Questions.eAdvising_ID
INNER JOIN Tbl_Counselor_Location_XRef ON Tbl_eAdvising.Location_ID = Tbl_Counselor_Location_XRef.Location_ID
INNER JOIN Tbl_Counselor_Master ON Tbl_Counselor_Location_XRef.Counselor_ID = Tbl_Counselor_Master.Counselor_ID
INNER JOIN Tbl_Student_Notes ON Tbl_eAdvising.Note_ID = Tbl_Student_Notes.Note_ID
AND Tbl_Student_Master.Student_ID = Tbl_Student_Notes.Student_ID
AND Tbl_Counselor_Master.Counselor_ID = Tbl_Student_Notes.Counselor_ID
AND Tbl_Counselor_Location_XRef.Counselor_ID = Tbl_Student_Notes.Counselor_ID
AND Tbl_Counselor_Location_XRef.Location_ID = Tbl_Student_Notes.Location_ID
INNER JOIN Tbl_Location_Control ON Tbl_eAdvising.Location_ID = Tbl_Location_Control.Location_ID
AND Tbl_Student_Notes.Location_ID = Tbl_Location_Control.Location_ID
WHERE Tbl_eAdvising.Student_Submitted Between '01/01/2012' And '06/13/2012'
AND Tbl_Location_Control.Location_ID = 1
AND Tbl_eAdvising.Student_Viewed_Response IS NOT NULL
AND Tbl_eAdvising.Response_Satisfactory = 0
GROUP BY minAltID, Tbl_Student_Master.Full_Name, Tbl_Counselor_Master.Short_Name, Tbl_eAdvising.Student_Submitted, Tbl_eAdvising.Viewed_By_Advisor,
Tbl_eAdvising.Advisor_Responded, Tbl_eAdvising.Student_Viewed_Response, Tbl_Location_Control.Location_Code, Tbl_eAdvising_Questions.Subject,
Tbl_eAdvising_Questions.Body, Tbl_eAdvising_Questions.Response, Tbl_eAdvising.Response_Satisfactory, Tbl_Counselor_Location_XRef.Active
ORDER BY Tbl_eAdvising.Student_Submitted
RESULT:
Msg 207, Level 16, State 1, Line 24
Invalid column name 'minAltID'.
Steve
We need men who can dream of things that never were.
June 12, 2012 at 5:58 pm
As minALTID is a derived column and does not eist in the context of the group by it woudl requrie the same derivation within the group by. Look forward to some real experts completely re-writing your code to make thsi work better but my suggestion would be to remove the column derivation into a new join usign a sub query and restructure your code as appropriate.
June 13, 2012 at 9:12 am
This was removed by the editor as SPAM
June 13, 2012 at 3:39 pm
Cheers dogramone, Stewart and Celko for your responses.
dogramone: I understand that it's a derived column and that is where the problem lies but SS lets me use this derived column in the Order By statement - why not the Group By?
Stewart: Thanks for the effort of looking at the query and the additional section does run but... minALtID is derived using the COALESCE because the first Student location xref alt ID is the one we want to use, but this may not be available in which case a null is returned - at which point we switch to using the Student Master altID. So using the Student Master AltID in the Group By doesn't solve the problem.
Celko: Thanks for the response. It's fairly obvious you are well versed in SQL - but is there any need to be arrogant and rude?
Comments like "The short answer is that you do not understand how a SELECT statement works. But a more useful answer is that you do not know anything about correct data modeling AND screwed up all the data elements." and "I think you need to get a book on basic data modeling and re-do what you have now." Are neither helpful nor appropriate. :rolleyes:
This world is not a perfect one and us normal people have to take jobs at companies that have existing applications and databases, coded, created and modelled by - yes, you guessed it - someone else. Recreating the entire database, remodelling it and re-writing the application that sits on top of it would be great in a perfect world but life just isn't like that.
So......
I have to work with what I have in front of me.
Thanks again to all for the replies. I would have preferred to run the counts within the query but it doesn't look like it's going to be possible so I have run the counts in the application and cured my problem.
So, unless you are extremely bored, twiddling your fingers looking for something to do - dont waste any further time trying to sort it out 🙂
I would still like to use my derived field in the Group By clause, especially seeing it forces me to put every field I am using in there - and lets me use the derived column in the Order By.... It kinda makes sense to be able to use it in both....
Steve
We need men who can dream of things that never were.
June 14, 2012 at 6:43 am
Ste_P (6/13/2012)
dogramone: I understand that it's a derived column and that is where the problem lies but SS lets me use this derived column in the Order By statement - why not the Group By?
It's because of the general order of processing. The GROUP BY is processed before the SELECT, so the derived column isn't defined at the point that the GROUP BY is processed. The ORDER BY, on the other hand, is processed after the SELECT, so the derived column can be used in the ORDER BY clause.
There was a recent Question of the Day about the order of processing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 14, 2012 at 9:06 am
Spot On! Simple, straight to the point - and makes perfect sense!
Thank you Drew
Steve
We need men who can dream of things that never were.
June 14, 2012 at 1:56 pm
Hope it all works and makes sense now. Don't worry about Joe, I completely agree with him and at times we all want to rant in the same manor. Our problem now days is that DB's are so popular but some so called experts are really application programmers just ruining relational theory by not doing the DB basics properly. It just means poor sods like you inherit rubbish, the companies you work for won't understand the size of the issue and of course can't afford to fix it. I hope now you understand a bit more about processing order of sql statements and why some things work and others that look right don't. I don't claim to have half the knowledge most of the forum posters have but am good at some of the very basics and like you have inherited many systems over the years that just need to be replaced. Where I work now one of the systems is storing data in old mainframe formats in SQL tables, a header row that tells you where in the footer the element you are looking for exists; its starting position and length. And the business just doesn't understand why things take so long to build and process.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply