Re: SQL Select Case Then Group By

Sorry to bother you all, I did figure out a solution.  However, if someone
has a better option, I'll be open to it.

SELECT Case
                  When STUNNO = 11111 then 'Internet'
                  When STUNNO = 22222 then 'Corporate'
                  ELSE 'All Restaurants'
       end AS Location, INT(STAMT) as Amount, COUNT(*) as Count

from MYFILE
GROUP BY CASE
                    When STUNNO = 11111 then 'Internet'
                    When STUNNO = 22222 then 'Corporate'
                    ELSE 'All Restaurants' END, INT(STAMT)
ORDER BY LOCATION, COUNT DESC, Amount

Thanks

I'll take into consideration about the WITH clause.

I guess I should have shown that part of the SELECT statement.  I am doing
functions on columns, so I did need to have the group by clause.  Also, I'm
going to be using this SQL statement with the TRANSFER DATA FROM iSeries.

Michael Schutte



                                                                           
             Michael_Schutte@b                                             
             obevans.com                                                   
             Sent by:                                                   To 
             rpg400-l-bounces@         rpg400-l@xxxxxxxxxxxx               
             midrange.com                                               cc 
                                                                           
                                                                   Subject 
             05/24/2006 03:02          SQL Select Case Then Group By       
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           





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.