× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. January 2005

AW: SQL grouping

Hi Lim,

didn't you get my solution yesterday?

Here again the SQL-Statement that works for 3 instances. For 8 you have
expand it:

with first  as (select phone as phone1, PhoneExt as Ext1,
                       Min(feature) as Min1
                  from phoneno
                  group by phone, phoneExt),
     second as (select phone as phone2, PhoneExt as Ext2,
                       Min(feature) as Min2
                  from phoneno join first  on phone1 = phone
                                          and ext1   = phoneext
                                          and min1   < feature
                  group by phone, phoneExt),
     Third  as (select phone as phone3, PhoneExt as Ext3,
                       Min(feature) as Min3
                  from phoneno join second on phone2 = phone
                                          and ext2   = phoneext
                                          and min2   < feature
                  group by phone, phoneExt)
select   phone1, phone2, min1, min2, min3
   from  first left outer join second on phone1 = phone2
                                      and ext1  = Ext2
               left outer join third  on phone1 = phone3
                                      and ext1  = Ext3

Birgitta


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Lim Hock-Chai
Gesendet: Freitag, 14. Januar 2005 18:43
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL grouping


seems like using temporary result field to in expression is not allow.  I
try this and it did work either:
SELECT Phone#, PhoneExt#, (%trim(featsString) concat feature)  as
featsString,
from rob/lim
group by phone#, phoneext#
order by phone#, phoneext#

Column FEATSSTRING not in specified tables.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Friday, January 14, 2005 9:14 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL grouping


I tried something like
SELECT Phone#, PhoneExt#,
 max(case when feat1 is null then feature else feat1 end)  as feat1,
 max(case when feat2 is null then feature else feat2 end)  as feat2,
 max(case when feat3 is null then feature else feat3 end)  as feat3
from rob/lim
group by phone#, phoneext#
order by phone#, phoneext#
Column FEAT3 not in specified tables.
And dropping the feat3 just bubbled up to FEAT2.


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/14/2005 09:59 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

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


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


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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.