Re: Using like on a result string

[Jeff Young <jyoung0950@xxxxxxxxx>]

You could try using DECLARE GLOBAL TEMPORARY TABLE as (Select ....) WITH
DATA WITH REPLACE

Jeff Young
Sr. Programmer Analyst

On Tue, Jan 12, 2016 at 11:23 AM, Craig Jacobsen <craigjacobsen@xxxxxxxxxxx>
wrote:

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