September 23, 2013 at 9:38 am
I've encountered a very weird error when using an OVER clause, which i cannot explain. I'd be interested if anyone else can tell me why this happened.
I was running the following code:
SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentProjects.ProjectID ORDER BY ParentProjects.ProjectID) = 1 THEN 1 ELSE 0 END
FROM Projects
INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectID
GROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost
and i got the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'Projects.ProjectID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Note that the column "Projects.ProjectID" is NOT in the select list and it IS in the GROUP BY clause, exactly the opposite of what the error claims!
Now, if if replace the PARTITION BY element and use an equivalent one from the primary Projects table (instead of the self join column), the code runs just fine:
SELECT ParentProjects.RollupAllProjectsCommittedTotalCost * CASE WHEN ROW_NUMBER() OVER (PARTITION BY Projects.TopLevelParentProjectID ORDER BY Projects.ProjectID) = 1 THEN 1 ELSE 0 END
FROM Projects
INNER JOIN Projects AS ParentProjects ON parentProjects.projectID = Projects.ParentProjectID
GROUP BY Projects.ProjectID, ParentProjects.ProjectName, ParentProjects.RollupAllProjectsCommittedTotalCost,Projects.TopLevelParentProjectID
Can anyone explain to me why this is happening?!?
September 23, 2013 at 11:50 am
you can't use the same column in partition and grouping
ParentProjects.ProjectID.
read about difference between group by and over partition by and you will see how they work
September 23, 2013 at 1:08 pm
I only put the ParentProject.ProjectID in the ORDER BY because i was trying to simplify the code as much as possible before posting. I get the exact same error, regardless of the column i put in the ORDER BY. Actually, i actually get the same error even if i replace the ROW_NUMBER() with, say, COUNT(ParentProject.ProjectID) and remove the ORDER BY entirely.
September 23, 2013 at 1:59 pm
Totally a shot in the dark because we don't have much in the way details...
See if this helps.
SELECT p.RollupAllProjectsCommittedTotalCost * CASE WHEN RowNum = 1 THEN 1 ELSE 0 END,
p.ProjectID,
p.ProjectName,
p.RollupAllProjectsCommittedTotalCost,
RowNum
FROM Projects p
cross apply
(
SELECT Projects.ProjectID,
ROW_NUMBER() OVER (PARTITION BY ParentProjects.ProjectID ORDER BY ParentProjects.ProjectID) as RowNum
FROM Projects p2
where p.ProjectID = p2.ProjectID
) x
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 5:05 pm
It turns out, I had forgotten to put ParentProjects.ProjectID in the GROUP BY clause (as the error was indeed claiming). But the error message said "Projects.ProjectID" because it was referring to the non-aliased table name which of course looked just like the primary table of the self-join!
So i guess the error message was "wrong" (as well as my SQL).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply