Re: SQL Partial "similar-ish" key matching, can it be done?

How's this?

select * from qtemp.cntry
where :newcode like (CountryCode concat '%')
or CountryCode like (:newcode concat '%')



Charles


On Tue, Dec 15, 2015 at 9:47 AM, Wilson, Jonathan <
piercing_male@xxxxxxxxxxx> wrote:

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)

0-117-00001X (X denotes checksum)
81-01154-00X
9222-4101-2X
9229-41-011X

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.