Re: SQL "like" from a subselect

[Booth Martin <booth@xxxxxxxxxxxx>]

Here's how I am doing a wildcard search, thanks to the replies here on 
Midrange a few weeks ago.  *LANGIDSHR did the trick of case 
insensitivity, plus adding the bonus of ORDER BY giving you today's 
version of normal.
...
   begsr *INZSR;
     exec sql set option Commit = *None;
     exec sql set option SRTSEQ = *LANGIDSHR;
...
     exec sql declare C1 cursor for
       select *
         from NAMESP
         where NALAST like :wSRCHLAST
         order by NALAST;
...
 wSRCHLAST could be populated with Roger or Rog or even %oge by the 
user, and you then cat onto the end an additional + "%"

Here is a screen shot:
http://martinvt.com/SQL_-beginers-/Sorting_Subfile/sorting_subfile.html.

On 11/25/2015 1:38 PM, Roger Harman wrote:
> 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