Hi David
There may be a better way but here's one way I used a while back to
generate a similar sequence. I guess to that you just need to add a
WHERE NOT IN vs. your catalogue numbers clause and a BETWEEN clause for
the maximum and minimum values
You need 2 files, containing 0-9 (I have called it nonetonine) and A-Z
(called upperAtoZ here)
select l.letter concat r.letter concat TRIM(CHAR(t.number))
concat TRIM(CHAR(h.number)) concat
TRIM(CHAR(ten.number)) concat TRIM(CHAR(u.number))
as sequence
from upperatoz l join upperatoz r
on l.letter = r.letter or l.letter <> r.letter
join nonetonine t on l.letter <> CHAR(t.number)
join nonetonine h on l.letter <> CHAR(h.number)
join nonetonine ten on l.letter <> CHAR(ten.number)
join nonetonine u on l.letter <> CHAR(u.number)
Hope that helps,
Martin
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of David Foxwell
Sent: 03 October 2007 10:31
To: rpg400-l@xxxxxxxxxxxx
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 message (including any attachments) is only for the use of the person(s) for whom it is intended. It may contain Mattel confidential, proprietary and/or trade secret information. If you are not the intended recipient, you should not copy, distribute or use this information for any purpose, and you should delete this message and inform the sender immediately.
midrange.com
