On 12-Jan-2016 07:13 -0700, Craig Jacobsen wrote:
I need to search multiple fields from 2 joined files. (there may not
be a record in 2nd file)
I am concatenating all the fields together and separating them with a
delimiter of | eg.
Is there a way to do a LIKE '%string%' on the result
Yes, a LIKE predicate can be performed against the result of the
concatenation.
But for most scenarios, there would be little justification to have
done so. Such scenarios could be likened to producing a spooled report
without selection, simply to enable scanning\parsing the report output,
to pare the results to only those report-lines that are of interest.
And mostly, having produced the final report from the row-data pared
within the query selection would be the better option. So in this
particular scenario, why not just perform selection on the values in the
columns [instead of a LIKE on the expression involving those columns]?
There is also the potential problem of false-positive results from
using a LIKE on the concatenated row-data; e.g. LIKE '%|WY%' might
intend to yield the result from a STATE_CODE column, but instead yield
a[n unexpected] result from a descriptive-text field ITEM_DESC. In that
case, the choice for selection on the columns vs on the expression will
seem more conspicuous; i.e. use of WHERE STATE_CODE='WY' instead of
WHERE Concat_Expression LIKE '%|WY%' is more accurate, but also can be
much faster.
Be aware that using a the LIKE predicate in a WHERE clause using the
LEFT JOIN so as to include unmatched rows, requires either coalescing
all column values in the concatenation expression, coalescing the
expression itself, or adding predicate logic:
OR Concat_Expression IS NULL
or do I have to get the string and then do a %scan on the string
after it is fetched.
FWiW, the SQL LIKE could be used in that context as well; i.e. as an
SQLhll program, the use of SQL predicates is not limited to just the
SELECT. I am not making a recommendation, just clarifying that
deferring to the HLL scanning is not required; if for example, the
effects of the LIKE [such as shared sort sequence] are more desirable.
I may have more than one string to LIKE or %SCAN on. There are about
100k records in the file.
dcl-ds DATA;
ItmString char(434) pos(1);
ItmNum char(20) pos(435);
end-ds;
I'm thinking it would be faster if the SQL handled it, but I may be
wrong.
The SQL can be faster, but without parallel data access, the
algorithms are effectively the same. Unless the LIKE is eliminated, the
effect from the query will be to read every row, further diminishing the
value of using the SQL to do the work. Having the program perform
multiple scans [more than one string to test] may also be /simpler/ in
the HLL than what might be accomplished using multiple "OR"ed LIKE
predicates on the SQL. Actual examples of data and inputs for the
search including how they are obtain, plus known boundaries [e.g. max
number of scans], would be good information to share when describing the
scenario.
Select
Trim(a.ItmNum)||'|'||
Trim(ItmDsc) ||'|'||
Trim(Vendor) ||'|'||
Trim(VnCode) ||'|'||
Trim(VnItmD) ||'|'||
Trim(CsDmti) ||'|'||
Trim(ItmSeq) ||'|'||
Trim(ItmSq2) ||'|'||
Trim(ItmUpc) ||'|'||
coalesce(trim(It60Ds),'-----') name
, a.ItmNum
FROM ItmMst a
left Outer Join
ItmAds b
On a.ItmNum = b.ItmNum
order by a.itmnum,b.itmnum
Given the equivalence of ItmNum betwixt, even with the possible NULL
result, the second column for ordering would seem redundant.?
Without the ORDER BY, the above can be encapsulated in a VIEW; if so
inclined, if for example the selection on the expression named NAME is
really sensible in the given scenario.
midrange.com
