January 28, 2013 at 4:28 am
Hey all,
I had a query that i didnt like - as it was a pain to maintain. Basically there are 6 levels of grouping that can be requested by a stored proc. So i had
IF @level =1
begin
select ....
end
ELSE IF @level=2
begin
select .....
end
I am sure you get the idea. Well i wondered if could do something like this
SELECT metricind
,LegalEntity
,SLKBU
,SLKDepartment
,SLKTeam
,CASE WHEN @Level = 6 THEN AssignedHandler ELSE '' END AS AssignedHandler
,Banding
,BandingOrder
,SUM(LedgerAmountGBP) AS LedgerAmountGBP
,SUM(Records) AS Records
,SLKTeam AS DashboardText
FROM DATATable
GROUP BY LegalEntity
,SLKBU
,SLKDepartment
, SLKTeam
,CASE WHEN @Level < 6 THEN AssignedHandler ELSE '' END
,metricind
,Banding
,BandingOrder
Obviously i would then move onto other columns for 5-1.... but this was a test.
However i am getting back
Column 'DATATable.AssignedHandler' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Now i read a couple of forum posts which seem to suggest you can do what i have asked.
So my question - can you do this (was the forum poster incorrect) and if so what am i doing wrong.
Else the only other way i can think of is building the SQL query. Dont fancy that either TBH
Any help greatfully received.
Dan
January 28, 2013 at 5:30 am
Your CASE statements need to match in the select & group by clauses - you have @Level = 6 in the select, and @Level < 6 in the group by.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply