Prompting for and passing partial search criteria to queries
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
May 3, 2006 3:29 PM
UPDATED:
December 6, 2009 11:23 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.