Re: SQL Select Case Then Group By

   It's not very elegant, but the following should do what you want:

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

   from MYFILE
   GROUP BY 
   ( Case
   When STUNNO = 11111 then 'INTERNET'
   When STUNNO = 22222 then 'CORPORATE'
   ELSE 'ALL RESTAURANTS'
   end)
   JJ

   This message is intended only for the use of the individual or entity
   named above and may contain information that is confidential. If the
   reader of this message is not the intended recipient, you are hereby
   notified that any dissemination, distribution, or copying of this
   communication is strictly prohibited. If you have received this
   communication in error, please notify the sender immediately by telephone
   and return the original to the above address. Thank you.

   On Wed, 24 May 2006 15:02 , Michael_Schutte@xxxxxxxxxxxx sent:

     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.