Load a subfile with clients selected by user

Another alternative to the BETWEEN to consider is a neato function
called SOUNDEX . . .
(It is available in my V5R4 box.)

You can create a select like this:
SELECT * FROM EMPLOYEE WHERE Soundex(LASTNAME) =
Soundex('SMITH')

It returns everyone with the last name of:
SMITH and SMYTHE and SUMMITT and SCHMIDT and SNEED and SWINT and
SZMYT and SANUTE and SANTO and SMOOT and SANTOYO and SANTHA and SNEYD
and SMITH, (even when a comma was captured)

I am thinking of hanging this off our regular employee search, calling
it Alternate Employee Search.
The sequencing I want to use shows the "exact" matches as the
first block of names,
then follows it with the other "close" names in a second block.

The SQL statement I came up with was this . . .
SELECT LASTNAME, FIRSTNAME, 0 as Diff
FROM EMPLOYEE WHERE LASTNAME = 'SMITH'

union all
SELECT LASTNAME, FIRSTNAME, Difference( Soundex(LASTNAME) ,
Soundex('SMITH' ) ) as Diff

FROM EMPLOYEE WHERE Soundex(LASTNAME) = Soundex('SMITH')

and PRLNM <> 'SMITH' order by Diff, PRLNM, PRFNM

Is there a better way to do this, by using WITH ?

I've also used something like this, as an alternative to the 'between'

clause.

Don't forget that you can compare values of host variables to

constants, to make application decisions in query.

where :ChristianName<>' ' and ChristianName=:ChristianName
and :Surname<>' ' and Surname=:Surname