Simple question on case statements and aggregating data

[Fanofmsu]

Hi - from my limited expereince in DB2, it appears that you can't
include a case statement in a group by clause - is this correct?

For example, my SQL looked like this:

SELECT CASE WHEN GROUP_CD<'00001' THEN 'ITS' ELSE 'NON' END AS TYPE,
COUNT(*) AS CNT FROM tblCLAIMS GROUP BY CASE WHEN GROUP_CD<'00001' THEN
'ITS' ELSE 'NON' END

My solution was to not initially group the data, and then summarize
the results in another query once the TYPE field had been populated.
Was my syntax incorrect or does DB2 not allow case statements in a
group by clause?

Thanks much!

Dave

[Hardy]

recheck your code pls... does "SELECT CASE WHEN GROUP_CD<'00001' THEN
'ITS' ELSE 'NON' END AS TYPE FROM tblCLAIMS " work?

I make two tests on the sample database shipped with db2 udb and both
work fine.

select case when edlevel <18 then 'low' else 'high' end as edu,
count(*) from employee group by case when edlevel <18 then 'low' else
'high' end

or

with data as ( select case when edlevel <18 then 'low' else 'high' end
as edu from employee) select edu,count(*) from data group by edu

[Fanofmsu]

Could it be our version of DB2, or the fact that I'm running my query
as an Access Pass-through? The SQL I posted above works fine in our SQL
Server environment. It's no big deal to use a subquery first, but I was
hoping to gain some knowledge as to whether DB2 supports case
statements in group by clauses. Thanks for trying!

[Knut Stolze]

Fa******@gmail.com wrote:

Could it be our version of DB2, or the fact that I'm running my query
as an Access Pass-through? The SQL I posted above works fine in our SQL
Server environment. It's no big deal to use a subquery first, but I was
hoping to gain some knowledge as to whether DB2 supports case
statements in group by clauses. Thanks for trying!

Yes, DB2 LUW does support CASE expressions in GROUP BY clauses.

To understand what goes wrong in your system, you should provide the
information which DB2 you are using and what was the exact error message.
Also, have you tried your statement directly at the DB2 command line?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany