RE: SQL grouping

That works.  Thanks Tony.  

It would be really neat if somebody can somehow figure out how to make this 
work for unreasonable number of feature codes.  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Lim Hock-Chai
Sent: Thursday, January 13, 2005 1:08 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL grouping


cool!.  Haven't try it yet.  But that should work.  Thanks.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tony Carolla
Sent: Thursday, January 13, 2005 11:51 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL grouping


Well, 2 to 3 features per phone number is promising, but also there
has to be a reasonable number of possible feature codes.  Here is an
example:

Select  Phone#, 
        PhoneExt#, 
        Max(Case Feature
                When 'ABC' Then 'ABC' Else ' ' End) As Feature_ABC, 
        Max(Case Feature
                When 'DEF' Then 'DEF' Else ' ' End) As Feature_DEF, 
        Max(Case Feature
                When 'XXX' Then 'XXX' Else ' ' End) As Feature_XXX
From    Lib.File
Group By Phone#, 
        PhoneExt# ;

You have to have one column for each possible feature code ('ABC',
'DEF', etc.)


On Thu, 13 Jan 2005 11:38:48 -0600, Lim Hock-Chai
<Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote:
> This is an one time fix.  There are only 2 to 3 features to deal with for a 
> given phone#.  I recevie the data in a spreadsheet.  However, our database is 
> in the later format.  Another word, pleeease give me the example.
> 
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tony Carolla
> Sent: Thursday, January 13, 2005 11:27 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL grouping
> 
> If there are a fixed, reasonable number of feature codes, and a fixed,
> reasonable number of codes for any given ph#/extension, then you could
> do it with UNIONS and CASE statements.  But this is probably not the
> case <---- pardon the pun.
> 
> If it is the case, let me know, and I could give you an example.
> 
> On Thu, 13 Jan 2005 09:28:48 -0600, Lim Hock-Chai
> <Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote:
> > David, let me know if this should not post here.
> >
> > I have a file that contains data looks something like this:
> >
> > Phone#         Phone Ext#    Feature
> > 1234567980      1234567       ABC
> > 1234567980      1234567       DEF
> > 1234567980      1234567       XXX
> > 1234569999      9994567       GXX
> > 1234569999      9994565       XXA
> > 6019991234                    ABC
> >
> > Is there a easy way to transform it to look like this using interavice SQL 
> > if I know that the most feauture an unquie phone+ext can have is 8?
> >
> > Phone#      PhoneExt#     Feat1    Feat2    Feat3    Feat4    Feat5    
> > Feat6    Feat7    Feat8
> > 1234567980   1234567       ABC      DEF      XXX
> > 1234569999   9994567       GXX      XXA
> > 6019991234                 ABC
> >
> > --
> > 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.
> >
> >
> 
> --
> "Enter any 11-digit prime number to continue..."
> --
> 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.
> 
> --
> 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.
> 
> 


-- 
"Enter any 11-digit prime number to continue..."
-- 
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.


-- 
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.