Re: How do I get Query Mgr Query to prompt the user....

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Dave

I think the ? is used for host variable in ODBC. Query/400 uses a 
colon followed by a merge field name, IIRC, although QM will use them 
like substitution variables. In QM the substitution variables are 
indicated with an ampersand before the name

select * from &LIB/&FILE

is an example, where &LIB is one variable and &FILE is another. I 
like using upper case names because it is easier in the STRQMQRY 
command where you specify the values. Let's say you created a QMQRY 
with the above statement and called it GENERICQRY.

Then you would use the following to show the contents of file 
QCUSTCDT in library QIWS

STRQMQRY GENERICQRY SETVAR((FILE 'QCUSTCDT') (LIB 'QIWS'))

I could have used lower case for the variable names because the 
iSeries automatically uppercases these things if there are no 
apostrophes. You could, I suppose, have separate UC & LC variables, 
like &a and &A, but I can think of only one good use for that.

The point of all this is, the statement is a string, and whatever you 
put in the setvar element replaces the variable in the statement. The 
setvar value must be a string, even numbers. Quoted strings are a bit 
of a problem, but someone came up with a brilliant way to handle 
them. E.g., with this statement in QMQRY TEST1

select * from myfile where charfield = &STRING and numfield = &NUMBER

strqmqry test1 setvar((string 'mystring') (number '35'))

results in

select * from myfile where charfield = mystring and numfield = 35

and will fail because it thinks that mystring is a column name. One 
solution is to put the quotes into the setvar value

strqmqry test1 setvar((string '''mystring''') (number '35'))

which is ugly but we have got used to it over the years. But the 
elegant way is to have a &Q substitution variable

select * from myfile where charfield = &Q&STRING&Q and numfield = &NUMBER

and use the following

STRQMQRY TEST1 SETVAR((STRING 'mystring') (NUMBER '35') (Q ''''))

which becomes

select * from myfile where charfield = 'mystring' and numfield = 35

which is the desired result, because &Q is replaced by the single 
apostrophe defined in the SETVAR parameter - remember, to get a 
single apostrophe you double up apostrophes within the opening and 
closing apostrophes that indicate a character literal.

Sub variable names are everything from right after the ampersand up 
to but not including another ampersand or a space.

I recommend the Query Management Programming Guide, it's at 
www.iseries.ibm.com/infocenter (pick any continent and release) in 
the Database section at V5R3, in the Printed versions section or some 
such. Has good treatment of the quoted strings stuff. And a lot more 
you don't even want to know, esp. about forms. You are much better 
off using Query Manager (STRQM) to do forms.

There is also the Query Manager guide or tutorial or whatever - nice intro.

BTW, once you put the sub vars into a statement, you can not prompt 
the statement in Query Manager - just the way it is.

And comments are done with double-dashes.

HTH
Have fun

Vern

At 07:29 PM 9/21/2005, you wrote:

> especially when the QM query is run from a CL or REXX EXEC?    I seem to
> remember putting something like ? marks in the query syntax/SQL but
> can't remember.
>
> OR better yet, what is the syntax for passing parms to QM so it can be
> invoked by a CL or REXX PGM?   The latter would be used if I built a
> front end screen and needed to pass the variables from the screen to the
> command.
>
> Thanks in advance,
>
> Dave
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.