×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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)
Imprint (publisher)
Book
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
books.
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
the file:
County/language
0
1
80
81
82
9222
9229
Not only that, but also no country code, to be added, can have as a part
of it a previous country code
0
01 << not allowed to be added
0177 << not allowed to be added
1
80
81
82
821 << not allowed to be added
9922
9925 << no problem - added
9929
Now in RPG its easy when adding records to test for a previous record
being "similar" with a something along the lines of:
SetLL NewCountryCode
ReadP
If CountryCodeRead = %subst(NewCountryCode:1:%len(CountryCodeRead))
// partial match hit - error.
endif
Like wise, adding a new country code that is a partial key match of a
longer already existing record can also be tested by:
0
1
8 << not allowed to be added
80
81
82
92 << not allowed to be added
922 << not allowed to be added
9222
9925
9926 << no problem - added
9229
SetLL NewCountryCode
Read
if %subst(CountryCodeRead:1:%len(NewCountryCode)) = NewCountryCode
// partial match hit - error
endif
Now my questions are: (V5R3 only as thats the highest level I can test
on.)
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
match next.)
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.
Many thanks in advance.
Jon
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.