RE: Joining files with a substring?

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Mark,

You can do the substring in your JOIN criteria.  Try this.

SELECT * FROM VINITEM A JOIN CASELABL B ON SUBSTR(A.ICITEM,1,7)=B.ITMBAS

Good luck!

Richard

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of J M Plank
Sent: Friday, July 18, 2008 12:04 PM
To: Midrange Systems Technical Discussion
Subject: Joining files with a substring?

I'm sure it is possible to join 2 files using a substring of a field in 1
file to a field in another file.  I have not found an example in the
archives or on the web for what I'm trying to do (probably because I'm
searching with incorrect parameters).

We have an item master file where item number is defined as 15A.  There is a
corresponding* case label file with item base defined as 7A.  I need to
select records where the first 7 positions for the item number match the
item base field.  Once I have this, there will be further selections, but
that should be straight forward (field a like this value).  This is a legacy
system that is being converted, and an analyst needs this information
retrieved.

The latest I tried is
RUNSQL REQUEST('Select substr(icitem,1,7) as base from vinitem a join
caselabl b on (a.base=b.itmbas)')
which gives me a "Column BASE not in table VINITEM in R37MODSDTA" error.

I have a conversion program which has this logic in /free and I may just
clone it (and would have been done by now if I had), but I'm sure this can
be done in SQL.

Thanks.
Mark Plank

* All programs using these files substring the item number, then chain to
the case label file.  You cannot query the two files as they have no common
fields, which is why I'm going the substring route.  This system is from the
'80s.