Essentially, yes. But, repeating the LIKE clause a few thousand times. If I could get the subselect to return 1 row at a time (sequentially) I'd probably be fine.
I also just realized I need information from both files and there's no commonality for a join.
RPG will be a real quick solution. Just looking to expand my use of SQL.
Thanks to all who replied.
________________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of rob@xxxxxxxxx <rob@xxxxxxxxx>
Sent: Wednesday, November 25, 2015 12:07 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL "like" from a subselect
Are you trying to do something like
select ...
where (lower(UPTEXT) like '%roger%') or (lower(UPTEXT) like '%sam%') or
(lower(UPTEXT) like '%pete%')
Here's where I am going to suggest something I know absolutely nothing
about: regular expressions.
From the 7.2 infocenter
What's new as of October 2014
REGEXP_xxx scalar functions and REGEXP_LIKE predicate for regular
expression handling
There's a regexp_instr and a regexp_substr
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Roger Harman <roger.harman@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 11/25/2015 02:39 PM
Subject: SQL "like" from a subselect
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
I'm trying to match employee names (first or last) to text in the user
profile. It's just a rough match to help operations in a task.
I have all the first & last names in a file and the user profile info in
another.
I've tried "where lower(UPTEXT) like (select srchname from namefile)" but
it fails with SQL0811 "Result of SELECT more than one row".
The search names are in the form of "%roger%". I can match singles in
STRSQL.
I can whip up an RPG with %scan() but curious if I can make the subselect
work. There are hundreds of profiles and thousands of employees.
Thanks in advance.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
OCEAN User Group
--
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.
--
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.
midrange.com
