RE: Extraction problem

["McGovern, Sean" <Sean.McGovern@xxxxxxxxxxxx>]

In the following SQL, I've assumed that file QSYS/QADBIFLD has more than
99999 records and have also used the INCR function (see
http://www.itjungle.com/fhg/fhg121405-story01.html): - 

with                                                      
alpha as                                                  
(select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' alpha from qsqptabl),
seq as                                                    
(select incr(0) as seq                                    
from qsys/qadbifld fetch first 99999 rows only),          
char1 as                                                  
(select substr(alpha,seq,1) as char                       
from alpha, seq fetch first 26 rows only),                
char2 as                                                  
(select substr(alpha,seq,1) as char                       
from alpha, seq fetch first 26 rows only),                
allcat as                                                 
(select a.char concat b.char                              
concat digits(cast(seq as dec(5))) as cat                 
from char1 a, char2 b, seq)                               
select * from allcat                                    
where not exists                       
(select 1 from yourcataloguefile where cat=yourcataloguefield) 

You will also need to add your own selection criteria (numbers available
between etc.).

Regards,
Sean McGovern

 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of David Foxwell
Sent: 03 October 2007 11:17
To: RPG programming on the AS400 / iSeries
Subject: Re: Extraction problem

Wot no SQL ?!


----- Original Message -----
From: <Peter.Colpaert@xxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, October 03, 2007 11:32 AM
Subject: Re: Extraction problem


> David,
>
> create a program that loops through all possible values between 
> AA00001 and ZZ99999, does a SETLL to the file, and writes the missing 
> (=available) numbers to a workfile, spooled file, etc.
>
> HTH,
>
> Peter Colpaert
> Application Developer
> PLI - IT - Kontich, Belgium
> -----
> Yoda of Borg are we.  Futile is resistance, assimilated will you be.
> -----
>
>
>
> "David Foxwell" <david.foxwell@xxxxxxxxx> Sent by: 
> rpg400-l-bounces@xxxxxxxxxxxx
> 03/10/2007 11:30
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> <rpg400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Extraction problem
>
>
>
>
>
>
> Hi,
>
> I have a file with catalogue numbers like this : AA00001 to ZZ99999.
>
> Is there a neat way to extract all the numbers available ( not being 
> used
> ) for a given prefix ?
>
> eg, the list of numbers available between PE00020 and PF99290, list of

> numbers with prefix AZ.
>
> Thanks
> --
> 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.