like query not running in pg

435 pts.
Tags:
AS/400 errors
AS/400 Query
AS400 RPGLE
RPGILE
hi i am running a query to select records which contains abc_ so i am running :

var1= 'abc_' + '%';

so i am running in rpg as

select name from table where name like :var1;

query is running but not selecting any records ..please help me out



Software/Hardware used:
as400

Answer Wiki

Thanks. We'll let you know when a new response is added.

And that would find records in table table in the field or column named name
<b>beginning</b> with <b>lower case </b>
abc_ and an underscore.

Phil

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    var1= 'abc_' + '%'; select name from table where name like :var1; Is column "name" a variable-length field? Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Two very basic items -- from the SQL Reference:
    • The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which the underscore and percent sign have special meanings. Trailing blanks in a pattern are a part of the pattern.
    Be aware the the "underscore" indicates a single-position wild-card character in the pattern. Any character in that position will match your pattern unless you include an "ESCAPE expression" clause. Also, if name is not a variable-length field and var1 contains trailing blanks after the "%", then the blanks can be part of the pattern too. I.e., if var1 contains three blanks after the "%", then name can match the pattern when it has three trailing blanks. This can be tricky stuff. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Since there are no responses, I'll add that one common resolution is:
    var1= ‘abc_’ + ‘%%%%%%’;
    If var1 is a 6 character variable, the resulting value would be 'abc_%%' after truncation. No trailing blanks remain in the pattern, so the pattern doesn't select values that have a blank at the end -- the pattern allows any characters in the 5th and 6th positions. And an ESCAPE clause might alter the SELECT statement to be:
    select name from table where name like :var1 ESCAPE '+';
    But that would mean that the pattern would need to be 'abc+_%%', and now var1 should be 7 characters long. Having data values that can contain 'wild' characters can make selection tricky. Trying to select fixed-length columns using generic specifications in embedded SQL can be tricky. Control over data values and use of variable length columns can help a lot. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following