× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



You could use a CTE, using the given SQL statement and do the search on the result of that SQL statement in the main SQL statement:

With tmp AS (

Select Trim(a.ItmNum)||'|'|| Trim(ItmDsc)||'|'|| Trim(Vendor)||'|'|| Trim(VnCode)||'|'|| Trim(VnItmD)||'|'|| Trim(CsDmti)||'|'||

Trim(ItmSeq)||'|'|| Trim(ItmSq2)||'|'|| Trim(ItmUpc)||'|'|| coalesce(trim(It60Ds),'-----') AS name, a.ItmNum

FROM ItmMst a left Outer Join ItmAds b On a.ItmNum = b.ItmNum
)
SELECT name, itmNum
FROM tmp
WHERE name LIKE ´%string%´
ORDER BY itmnum

Do not know how well this will perform.

Regards,
Carel Teijgeler


Op 12-1-2016 om 15:13 schreef Craig Jacobsen:
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 or do I have to get the
string and then do a %scan on the string after it is fetched.

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.


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


Thank you,


Craig



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.