MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » July 2008

Re: Joining files with a substring?



fixed

Mark

If this is a one-off, what I say maybe can't matter - if possible, avoid expressions in JOIN clauses and in WHERE clauses - the optimizer can't use an index when an expression is involved (recent discussion here might suggest there are exceptions in some cases, if the optimizer is smart enough). So it is hoped not to have 2 values strung together in one field- overloading is not the way to go anymore.

It sounds like you might be trying to do just this - split the 2 values. It will fare well in the future.

Regards
Vern

-------------- Original message --------------
From: "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
wrote:

Have you tried something like this?

Select
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact