Prompting for and passing partial search criteria to queries

pts.
Tags:
AS/400
IBM DB2
I?ve built a query, based on table containing public school student demographic data, using query utilities (i5/os, V5R3) which prompts for a value when run. The criteria in the ?select records? panel is FIELDLASTNAME EQ :FIELDLASTNAME. When I run STRQMQRY QMQRY(LIBRARY/QUERYNAME) ALWQRYDFN(*YES) QMFORM(*QMQRY) from the a command line, the system prompts me for FIELDLASTNAME. This is what I want. However, as it stands now, users have to type in a complete last name enclosed by single quotes for the query to return results. I?ve been trying with no success to get it to work with a partial last name search. Additionally, query utilities won?t allow me to combine the dependent column parameter (:FIELDLASTNAME) with the LIKE test. I just want to be able to enter something like SM% to pull all SMITHS, etc. when prompted and keep things simple. Any insights would be greatly appreciated.

Answer Wiki

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

Jimmer: you need to build a from/to range parms to pass to the STRQMQRY — you’ll need a CL pgm to do this. The FROM field value will be what the user keyed in and the TO field value will be the what the user keyed in with “9999”s being concatenated to the end of the TO field value. HTH

======================================================

It’s more complex because you’re using the long obsolete Query/400 instead of simply using a QM query directly, but I <i>think</i> the overall problem is the same. Here’s your spec:<pre>FIELDLASTNAME EQ :FIELDLASTNAME</pre>A QM query SELECT statement that does the same thing would be:<pre>FIELDLASTNAME = &FIELDLASTNAME</pre>If a user typed this for input:<pre>SM%</pre>…the resulting clause would be:<pre>FIELDLASTNAME = SM%</pre>Unfortunately, that’s not valid syntax. There are no quotes around the literal, so SQL can’t recognize that it’s supposed to be a literal. SQL has no way to know that you didn’t want to check against something named SM and mistakenly typed a ‘%’ in the wrong position.

If you want it to work, you need the clause to end up looking this way:<pre>FIELDLASTNAME = ‘SM%'</pre>That is, either you must put the quotes in the query or the users must type the quotes around the characters.

Take your pick. One way or the other, the query requires quotes.

You can’t put the quotes directly in the SELECT statement. If you do, then the query can’t recognize that there’s a substitution variable in there. The query will treat the quotes as if they’re surrounding a literal.

So, the quotes have to be inserted some other way. Most often, programmers will take the input from the user and put quotes around the value in program code. Since you’re using Query/400, that might be your only choice.

If you’d dump Query/400, it could be easier.

Tom

Discuss This Question: 1  Reply

 
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
  • Vatchy
    I created query RUNSQLTST as a QMQRY: SELECT * FROM SFPARMS WHERE KEYFIELD LIKE &KEY After creating it: CRTQMQRY QMQRY(RUNSQLTST) SRCFILE(QQMQRYSRC) I ran this from the command line: STRQMQRY QMQRY(RUNSQLTST) SETVAR((TEST '''RSE%''')) Note the extra quotes around the value. This ran correctly and select everything that started with RSE. I hope this helps.
    1,410 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