Joining Incompatible Data

Published: 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

Common Sense Comparisons

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot