AW: SQL Select Case Then Group By

Hi,

because you are not using a column function such as SUM() or Count(), you
don't need a goup by clause, just use a SELECT distinct instead.

SELECT Distinct
       Case When STUNNO = 11111 then 'INTERNET'
            When STUNNO = 22222 then 'CORPORATE'
            ELSE 'ALL RESTAURANTS'
       end
from MYFILE

If you need some column functions you have to repeat the case statement in
the Group by clause.
An other solution would be to use a Common Table expression (that creates
something like a temporary view).

With x as (SELECT Case When STUNNO = 11111 then 'INTERNET'
                       When STUNNO = 22222 then 'CORPORATE'
                       ELSE 'ALL RESTAURANTS' as Location,
                  end,
                  Field1
             from MYFILE)
select Location, Sum(Field1), count(*)
   from x
   group by location


Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von
Michael_Schutte@xxxxxxxxxxxx
Gesendet: Mittwoch, 24. Mai 2006 21:03
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL Select Case Then Group By



Guys, I have a file that has a number of locations.  I want to group all
locations less then 9995 as restaurants and then have two other locations
to be by themselves.

I have a workable select statement.

SELECT Case
          When STUNNO = 11111 then 'INTERNET'
          When STUNNO = 22222 then 'CORPORATE'
          ELSE 'ALL RESTAURANTS'
       end
from MYFILE


However, I want to be able to group by the result of the case.   For
instance, (this doesn't work)

SELECT Case
                  When STUNNO = 11111 then 'INTERNET'
                  When STUNNO = 22222 then 'CORPORATE'
                  ELSE 'ALL RESTAURANTS'
       end AS Location

from MYFILE
GROUP BY Location

It states that Location doesn't exist.  I understand why I get this
message, but I'm wondering how can I get this to work. If I'm able too.

I've tried to Group By STUNNO... but I get a record result for each unit,
even though it will return "ALL RESTAURANTS"... instead of the actual unit
number.

Thanks.


Michael Schutte

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.