Joining Incompatible Data
January 13, 2010 Hey, Ted
|
A physical file contains a nine-byte character acknowledgement number field that consists of a single letter followed by eight digits. I would like to change this field to two fields: a one-byte character and an eight-byte numeric field so that I could join the file to other files that have only the eight-digit numeric value. I realize that I could do a conversion to redefine the field, but that would require extensive changes to our applications. Is there an easier method to redefine the field, such as in a logical file? –David You probably don’t have to redefine the field, David. It depends on the tools you use to join the files. SQL, for example, permits you to join character to numeric. Query for i, or whatever they call it these days, doesn’t. Let’s look at an example. Assume two database files, REF and ACK. REF contains the nine-byte character field you described (REFNO), and ACK contains the eight-digit numeric field (ACKNO). The following SQL command joins the two files.
select r.whatever, a.whatever
from ref as r
left join ack as a
on substr(r.refno,2,8) = a.ackno
While this works, I prefer to be more obvious about it.
select r.whatever, a.whatever
from ref as r
left join ack as a
on decimal(substr(r.refno,2,8),8,0) = a.ackno
If you do need a permanent object, you won’t be able to use a logical file, because a logical file will not convert an alpha substring to numeric. However, an SQL view will.
create view RefAcks as
select r.key,
substr(r.refno,1,1) as RefCode,
decimal(substr(r.refno,2,8),8,0) as RefAckNo
from ref as r
You can use the view in SQL queries, just as you can a table.
select r.whatever, a.whatever
from refacks as r
left join ack as a
on r.refackno = a.ackno
And the view will work in those tools, like Query, that require join fields to be of compatible data types. SQL views do lots of things that logical files can’t do. It’s not too late to make another New Year’s resolution: when possible use SQL views instead of logical files. –Ted RELATED STORIES Joining on Incompatible Fields in Query/400
|
