On 16-Dec-2015 05:14 -0600, Wilson, Jonathan wrote:
Given the 10 digit ISBN holds a country code within it, and there
is no indication of how many digits make up the country code...
how would I do something along the lines of:
select SomeFields
from BiblFile
join with Countryfile
where
BiblFile.ISBN contains partial match with CountryFile.Country.
So [BiblFile]:
011700001X TitleA
810115400X TitleB
922241012X TitleC
922941011X TitleD
and [CountryFile]:
0 English
1 English
80 Czech Republic
81 India
82 Norway
9229 Guatemala
OK, since clarified, the CountryFile is an effective parent file, for
which a Trigger from my prior reply could be used to enforce the
/constraints/ on the data [per inability of a CHECK CONSTRAINT to do
so]. I say /effective parent/, instead of actual parent, because the
BiblFile would need to have been defined reflective of the columns I had
described from my interpretation of the OP, whereby there are three
separate columns, and the first should be a match to the first column
(country) of the CountryFile. That would allow Referential Integrity
(RI), whereby CountryFile is the parent for the country data stored in
the BiblFile.
Note: For normalization, the separate sections of the ISBN are best
stored physically as separate, and then combined for output.
creates
011700001X TitleA 0 English
810115400X TitleB 81 India <-- <ed:> '1' changed to '81'
922241012X TitleC
922941011X TitleD 9229 Guatemala
Using your assumption of varying length fields, no trailing spaces.
I get the feeling that it's something to do with joining the country
file to the bibliographic file, but in reverse - kind of.
Select SomeFields
from BiblFile and CountryFile where rightjoin
(CountryFile.Country *cat '%') like Biblfile.ISBN
Making the data functional for RI would also make the join much
simpler [for the database]. Also allows adding some constraints to
ensure the combined columns of the ISBN do not exceed restrictions. And
finally, as three distinct field instead of one, the combined ISBN can
still be made visible and functional for both RLA and SQL as a
key\INDEX. The following script might give some ideas.
create table CountryFile
( country_code for country varchar( 6) not null
, country_desc for text_desc varchar(45) not null
, constraint CountryFile_PK primary key (country)
)
;
create trigger [from a prior reply, but on CountryFile]
; -- [
http://archive.midrange.com/midrange-l/201512/msg00460.html]
insert into CountryFile values
( '0' ,'English' )
,( '1' ,'English' )
,( '80' ,'Czech Republic' )
,( '81' ,'India' )
,( '82' ,'Norway' )
,( '9229' ,'Guatemala' )
;
create table BiblFile
( country varchar( 6) not null
, imprint varchar( 6) not null
, book varchar( 6) not null
, chksum char not null
, title varchar(95) not null
, constraint BiblFile_FK foreign key (country)
references CountryFile (country)
)
;
insert into BiblFile values
('0' ,'117' , '00001' ,'X' ,'TitleA')
,('81' ,'01154', '00' ,'X' ,'TitleB')
,('9222' ,'4101' , '2' ,'X' ,'TitleC')
; -- the 3rd row would be blocked from insert per RI
-- expected error in above is SQL0530 or sqlcode -530
call qsys2.qcmdexc
('CHGPFCST FILE(BIBLFILE) CST(*all) STATE(*DISABLED)')
; -- disabled constraint to move on to review JOIN
insert into BiblFile values
('9222' ,'4101' , '2' ,'X' ,'TitleC')
,('9229' ,'41' , '011' ,'X' ,'TitleD')
;
create view BiblView as
( select
VARCHAR( COUNTRY concat '-' concat IMPRINT concat
'-' concat BOOK concat CHKSUM, 12 ) as ISBND
, VARCHAR( COUNTRY concat IMPRINT concat
BOOK concat CHKSUM, 10 ) as ISBN
, title
from BiblFile
)
;
select * from BiblFile
-- report output from above query:
ISBND ISBN TITLE
0-117-00001X 011700001X TitleA
81-01154-00X 810115400X TitleB
9222-4101-2X 922241012X TitleC
9229-41-011X 922941011X TitleD
******** End of data ********
-- then the query:
select
VARCHAR( b.COUNTRY concat b.IMPRINT concat
b.BOOK concat b.CHKSUM, 10 ) as ISBN
, varchar(b.title, 6) as title
, c.country_code
, c.country_desc
from BiblFile as b
left join
CountryFile as c
on b.country=c.country
; -- the report output from above query:
ISBN TITLE COUNTRY_CODE COUNTRY_DESC
011700001X TitleA 0 English
810115400X TitleB 81 India
922241012X TitleC - -
922941011X TitleD 9229 Guatemala
******** End of data ********
-- then the corrections to the data, put RI back in force,
-- and add the extra data validation that can be done via
-- constraints instead of the TRIGGER
insert into CountryFile values
( '9222' ,'Parent entity' )
;
call qsys2.qcmdexc
('CHGPFCST FILE(BIBLFILE) CST(*all) STATE(*ENABLED)')
; -- enabled constraint after parent row was added
alter table BiblFile
add constraint BiblFile_CK_varlen_data_must_equal_ten
check ( length(COUNTRY) + length(IMPRINT)
+ length(BOOK) + length(CHKSUM) = 10 )
; -- ensure ISBN, combined vlen data length, is always ten
insert into BiblFile values
('82' ,'06124', '001' ,'X' ,'TitleN')
; -- INSERT, UPDATE, or MERGE not allowed by CHECK constraint.
-- above expected error is sqlcode -545 aka SQL0545
-- easily enough added to the trigger instead, however
create index BiblFile_ISBN on BiblFile
( VARCHAR( COUNTRY concat IMPRINT concat
BOOK concat CHKSUM, 10 ) as ISBN
) rcdfmt ISBNplus add all columns
; -- make an LF and keyed Access Path (ACCPTH) for the
-- derived expression as result for combined column ISBN
midrange.com
