RE: Extraction problem

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.