Select Syntax problem Group by with Case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Syntax problem Group by with Case

Author

Topic

cswhitfield
Starting Member

2 Posts

Posted - 2004-12-03 : 13:50:09

I have a working example of this but can't see my problem with this revised select statment: Can someone tell me what the problem is?
The error I am getting is 'not a GROUP BY expression'

select
case
when GLPB.obj_consolidatn_num = 'SUB9' then 'Recharges'
when GLPB.obj_consolidatn_num = 'INC0' then
case
when GLPB.object_num = '006R' then 'Refunds'
else 'Income'
end
end as description,(-1.0*sum(GLPB.fiscal_period_trans_total_amt)) as total
from finance.general_ledger_period_balances GLPB, Finance.object_consolidation OC
where GLPB.fiscal_year = #fiscalyear# and GLPB.fiscal_period > '00'
and GLPB.chart_num = '3' and GLPB.org_id = '#orgid#'
and GLPB.acct_num = '#acctnum#' and GLPB.balance_type_code = 'AC'
and (GLPB.obj_consolidatn_num = 'INC0' or GLPB.obj_consolidatn_num = 'SUB9')
and OC.fiscal_year = GLPB.fiscal_year and OC.chart_num = GLPB.chart_num
and OC.obj_consolidatn_num = GLPB.obj_consolidatn_num
group by
case
when GLPB.obj_consolidatn_num = 'SUB9' then 'Recharges'
when GLPB.obj_consolidatn_num = 'INC0' then
case
when GLPB.object_num = '006R' then 'REFUNDS'
else 'Income'
end
end

order by
case
when GLPB.obj_consolidatn_num = 'SUB9' then 'Recharges'
when GLPB.obj_consolidatn_num = 'INC0' then
case
when GLPB.object_num = '006R' then 'Refunds'
else 'Income'
end
end

.......

chadmat
The Chadinator

1974 Posts

Posted - 2004-12-03 : 14:22:52

I'm not 100% on this, cause I never tried it, but I don't think you can use CASE in the Group By or Order By clause.

Try changein those to just

GROUP BY GLPB.obj_consolidatn_num, GLPB.object_num
ORDER BY GLPB.obj_consolidatn_num, GLPB.object_num

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

cswhitfield
Starting Member

2 Posts

Posted - 2004-12-03 : 14:38:59

Chad - Thanks for the suggestion, I tried it but still no joy, I get the same error. The Select>Case Group by>Case and Order by>Case work in other parts of this same document (I've been asked to add enhancement to prior employes's project). So it seems that it's something else that's causing the problem. Still, I like the simplified code and will keep that. -Carolyn

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 14:55:19

It is well possible to have case statements in the order by or group by clauses.
If in the group by clause, the case statement has to be in the select list as well.

As to the query above, I can't see anything wrong with it.
Try to break it apart &&|| simplify somewhat and run one piece at a time.

rockmoose

chadmat
The Chadinator

1974 Posts

Posted - 2004-12-03 : 15:13:54

Another learning experience. I never knew you could do that.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 15:31:06

The order by even allows "dynamic" ordering.
I thought this was wierd when I first saw it:

declare @orderdir int

select i%2, i from
( select 1 as i union select 2 union select 3 union select 4 ) t
order by case @orderdir when 1 then i else i%2 end

set @orderdir = 1

select i%2, i from
( select 1 as i union select 2 union select 3 union select 4 ) t
order by case @orderdir when 1 then i else i%2 end

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources