A temporary table is worse, because you need some time to write the
information.
Instead of a temporary table I'd generate an SQL view, which is based on a
select statement, but does not include any data.
An SQL View is some kind of logical file and can be used (with SQL) and can
be used like an table or physical file.
Create View YourSchema/YourView
As (Select ..... );
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Craig Jacobsen
Gesendet: Tuesday, 12.1 2016 17:24
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Using like on a result string
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.
midrange.com
