RE: Using like on a result string -- MIDRANGE-L

Thank you both Carel and John.
Seems to be pretty fast too.
Just wondering if creating a table when the program loads in qtemp and then
doing the likes would be faster?
If so how would I create a temp table, and then do the searches later in the
program?
Hopefully I'll find an answer before I get any responses.

Thanks again,

Craig

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John
R. Smith, Jr.
Sent: Tuesday, January 12, 2016 10:35 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Using like on a result string

I would build it something like this (syntax may not be 100% as I "coding in
email")

With data as (
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
)
Select
Name, itmnum from data
Where name like '%string%'

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Craig
Jacobsen
Sent: Tuesday, January 12, 2016 9:13 AM
To: 'Midrange Systems Technical Discussion'
Subject: Using like on a result string

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

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.