This question is based in part on an ISBN structure, but is not strictly
following the rules.
Both short and long answers are appreciated, even ones that say "look at
the ... statement in the ... manual" or "this link does something
similar, you'll have to adapt it to DB2/i" as I'm working with very very
little experience of SQL (basic selects/inserts/updates is my current
level of experience).
An ISBN is made up of 3 parts (Excluding the new prefix, and checksum).
Country (Also language, as "English" is denoted by 0 and 1)
All the parts are variable in length, so if a country produces few books
its country code is longer than a country producing a lot of titles.
The same holds true of the Imprint, someone like OUP has a short imprint
number but a long title number split. JoeBlogsPublishing has a long
imprint and a short title number split as he will publish hardly any
A couple of examples (not valid re:checksum or actual codes)
Now each section has to be "partial" unique, and the concatenation of
sections also have to be unique. (actually I'm not totally sure if the
imprint has to be "partial" unique on its own, or only as a
concatenation with the country) Assume the following already exist in
Not only that, but also no country code, to be added, can have as a part
of it a previous country code
01 << not allowed to be added
0177 << not allowed to be added
821 << not allowed to be added
9925 << no problem - added
Now in RPG its easy when adding records to test for a previous record
being "similar" with a something along the lines of:
If CountryCodeRead = %subst(NewCountryCode:1:%len(CountryCodeRead))
// partial match hit - error.
Like wise, adding a new country code that is a partial key match of a
longer already existing record can also be tested by:
8 << not allowed to be added
92 << not allowed to be added
922 << not allowed to be added
9926 << no problem - added
if %subst(CountryCodeRead:1:%len(NewCountryCode)) = NewCountryCode
// partial match hit - error
Now my questions are: (V5R3 only as thats the highest level I can test
1) how do I test both conditions in SQL, using two SQL operations?
(Actually return the partial match hits with two selects - select from
file when partial match previous. And - select from file when partial
2) Is is possible to perform both tests with only one SQL statement and
if so how? (a single select) select from file when partial match
previous or partial match next.
3) How would I express: insert into file where partial previous and
partial next does not exist.
4) How would I express this as a RI constraint so the DB handles the
prevention of incorrect partial key match adds. (I already prevent
duplicates via unique key) Assuming such a thing is possible? eg: on
insert or update reject if partial previous or partial next found.
Please note, these will be entered SQL at the green screen.
This mailing list archive is Copyright 1997-2020 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