× 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

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.

As an Amazon Associate we earn from qualifying purchases.

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

This mailing list archive is Copyright 1997-2024 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.