Re: Embedded RPG and the WHERE LIKE

On 16-Nov-2015 15:44 -0600, Booth Martin wrote:

Lots of good answers. <<SNIP>>

However....
Smi% works. %mit% works. %ith does not work. Further testing shows
that Smith does not work either. It would seem to me that these
would all work or, none?

The code is:
...
d wSRCHLAST s 35 varying
...
wSRCHLAST = %trim(SRCHLAST);
...
where NALAST like :wSRCHLAST

<<SNIP>>

I had started a reply to the OP with the following, but then just decided not to reply at all to a poorly-described scenario that was sure to result in inferences being made by readers and thus either presumptuous or ill-informed replies:

What had been requested in the SQL and what had been defined, were not well-described. Though easily, those could have been provided in a quite exacting manner, as effective [actual or pseudo-] code; i.e. no DDL [nor any sample data, though readers might sufficiently\correctly infer], no actual embedded SQL statement, and no definitions of program\host variables were given, but to describe the scenario they are easily enough included so as not to make the readers have to guess what they might have been.

While the code snippet being offered is a big improvement in this followup over what was offered in the OP, similarly in reply to this most recent followup message, I might suggest that even the chosen code snippets are lacking what I allude to just above as the makings for a /well-described/ scenario. Consider that responses will tend to be better both when complete information is given and when explicit /code/ examples vs /non-code phrases/ are used to describe the inputs and the outputs. For example rather than saying "Smith does not work", for which one might infer any of several possible implications for inputs and output, why not suggest something like the following for which there can be little doubt about both what is "Smith" and how the value "Smith" is utilized:

The following yields an empty result-set despite the file
being queried [that should have been *described previously*
with the DDL and even better with the sample data] having one
row for which the column NALAST has the value of 'Smith':

The code is:
...
d wSRCHLAST s 35 varying
...
wSRCHLAST = 'Smith' ;
...
exec sql ... where NALAST like :wSRCHLAST
; ...

And given that more explicit description, one might be able to suggest, and probably quite definitively, that the effect is due to one of: the definition for the column NALAST being fixed-length vs variable length [the DDL would have revealed], an improper /expectation of/ vs /what is/ the actual data in that column NALAST for that specific row of data [an INSERT INTO of the sample data would have revealed], or the failure to have appended the percent sign as the zero-to-many character replacement pattern so as to allow the SQL selection to ignore trailing blanks in the values for the NALAST column [the DDL or sample data would reveal, according to one's perspective]. Again, with both the DDL and sample data in addition to the definition of the [host] variable(s) and the query, appropriate and helpful responses by the reviewers is made _so much_ easier... and prevents worthless banter that arrives as an unhelpful reply, in the form of an inquiry eliciting more details or a suggestion that is not applicable or will prove to be unfruitful.

And with the DDL for the access paths given along with the actual implementation see for the queries giving the unexpected output and those giving the expected output, a reviewer might also be able to point-out a caveat whereby different output could be the effect when the results are derived from an index-scan vs derived from a table-scan. I doubt that is the issue here... but the point is, the less offered in a somewhat exacting description of the scenario [e.g. in /code/ vs /words/], the more a reader has to /assume/ vs to /know/, thus without their assumptions being stated in their replies or being inferred [as conspicuous] from their replies, and what they offer to assist the OP may not be all that helpful... as apparently has occurred with this topic.