RE: SQL Select Case Then Group By

I think ordinal reference is only allowed on the order by clause....  Group by 
requires explicitely referenced fields, unless this has changed in a recent 
release. 

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Wednesday, May 24, 2006 3:43 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL Select Case Then Group By



Michael,

Birgitta's solution is probably the simplest.  However, I think you
could also substitute 1 for the group by value.  This will use the first
field selected as the grouping field.

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

Rick

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
> Michael_Schutte@xxxxxxxxxxxx
> Sent: Wednesday, May 24, 2006 2:03 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: 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

Privileged and Confidential.  This e-mail, and any attachments there to, is 
intended only for use by the addressee(s) named herein and may contain 
privileged or confidential information.  If you have received this e-mail in 
error, please notify me immediately by a return e-mail and delete this e-mail.  
You are hereby notified that any dissemination, distribution or copying of this 
e-mail and/or any attachments thereto, is strictly prohibited.