Re: Joining files with a substring?

["J M Plank" <plank.computer@xxxxxxxxx>]

Ah, that did it.  Thanks Scott!  I was too focused on the forest and not the
trees.  I don't need the fields from the caselabl file - I just need to
select out records from caselabl.

This gives me a start (we're looking at specific csltxt values):
'select icitem from vinitem a join caselabl b on
substr(a.icitem,1,7)=b.itmbas where b.csltxt like ''@%'''

Further background on this legacy system - the csltxt field has two meanings
- how to create lot codes and the label description so we could have the
following:
@MDY2-H@ - lot code only
DUCKLING BREAST - text description only
@Y1J-H@  RAW    Frozen - both lot code and text description
I'm breaking this field into two separate fields when converting the legacy
system to our production system and found some lot code values that weren't
correct.  I was then asked for the associated item numbers.

Mark Plank

On Fri, Jul 18, 2008 at 12:30 PM, Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>
wrote:

> Have you tried something like this?
>
> Select <whatever cols you need>
>   from vinitem a
>   join caselabl b on substr(a.icitem,1,7)=b.itmbas
>
> Seems to me that the output of the select can't be used as the join
> criteria.  (After all, the fields output by the select are DETERMINED by
> the join criteria!) therefore, you need to repeat the substr()
> expression in the join clause.