RE: Problems with Group by and case in sql

The following shoud work: -

SELECT eeun, eedpt, plbemp, eenam, plbbg1, case PLBPTP when 'CO' then 'CALLOUT' Else 'OT' End, sum(plbhrs)
FROM PRLBH, EEMP
WHERE PLBEMP = EEENO and PLBPTP in('CO','OH', '6D','7D','OG')
and EETDT = 0 and EEDPT in (select PRDPT from PROVRDPT00 where
PRNAME = 'BRANDTNS')
and PLBEDT = 20100228
GROUP BY PLBEMP,EENAM,PLBBG1,EEUN, EEDPT , 6

or

With t as
(SELECT eeun, eedpt, plbemp, eenam, plbbg1, case PLBPTP when 'CO' then 'CALLOUT' Else 'OT' End plbptp, plbhrs
FROM PRLBH, EEMP
WHERE PLBEMP = EEENO and PLBPTP in('CO','OH', '6D','7D','OG')
and EETDT = 0 and EEDPT in (select PRDPT from PROVRDPT00 where
PRNAME = 'BRANDTNS')
and PLBEDT = 20100228)
Select eeun, eedpt, plbemp, eenam, plbbg1, PLBPTP, sum(plbhrs)

From t

GROUP BY PLBEMP,EENAM,PLBBG1,EEUN, EEDPT , PLBHRS


________________________________

From: midrange-l-bounces@xxxxxxxxxxxx on behalf of Vern Hamberg
Sent: Fri 19/11/2010 22:03
To: Midrange Systems Technical Discussion
Subject: Re: Problems with Group by and case in sql



Mike

In order to be in the select list, it has to have a name (maybe
expression) in the group by list or be an aggregate function. Try
putting an "as column-name" after the CASE-END construct.

I don't know that this will work, but worth a try.

Vern

On 11/19/2010 3:03 PM, Smith, Mike wrote:

I have a SQL I'm trying to write. My first attempt is trying to use a
case statement in a GROUP BY clause.

SELECT eeun, eedpt, plbemp, eenam, plbbg1, plbptp, sum(plbhrs)
FROM PRLBH, EEMP
WHERE PLBEMP = EEENO and PLBPTP in('CO','OH', '6D','7D','OG')

and EETDT = 0 and EEDPT in (select PRDPT from PROVRDPT00 where
PRNAME = 'BRANDTNS')

and PLBEDT = 20100228

GROUP BY PLBEMP,EENAM,PLBBG1,EEUN, EEDPT ,
case PLBPTP when 'CO' then 'CALLOUT'

Else 'OT'
End

I get errors telling me PLBPTP or expression in select list not valid

If I remove the case on the group by I get results, but not what I would
like ideally.

In researching this, I came across a CTE example that I worked like this


with x (a,b,c,d,e,f) as (SELECT eeun, eedpt, plbemp, eenam, plbbg1,
sum(plbhrs) case plbptp when 'CO' then 'CALLOUT'
when 'OT' then 'OVERTIME'
when '6D' then 'OVERTIME'
when '7D' then 'OVERTIME'
when 'OG' then 'OVERTIME'
else 'OTHERS'
end
from PRLBH, EEMP
WHERE PLBEMP = EEENO
and PLBPTP in('CO','OH', '6D','7D','OG') and EETDT = 0 and EEDPT
in (select PRDPT from PROVRDPT00 where PRNAME = 'BRANDTNS') and
PLBEDT = 20100228)
select a,b,c,d,e,f from x group by c,d,e,a,b,f


On this one I get token PLBPTP not valid on my case statement.

This one will work if I remove the sum(plbhrs), but I need it in my
final results.

Any idea what I'm doing wrong on either one of these.

Thanks for any help

Mike

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.