|
wow!!!. That works very well actually. Even better because it shows features consecutively (no blank feature in between). This is it folks. I'm going to save this for future reference. Thanks Hauser -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of HauserSSS Sent: Friday, January 14, 2005 3:04 PM To: RPG programming on the AS400 / iSeries Subject: 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. -- 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 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.
midrange.com
