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

Dave

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


select * from &LIB/&FILE

is an example, where &LIB is one variable and &FILE is another. Ilike using upper case names because it is easier in the STRQMQRYcommand where you specify the values. Let's say you created a QMQRYwith the above statement and called it GENERICQRY.

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


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

I could have used lower case for the variable names because theiSeries automatically uppercases these things if there are noapostrophes. 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 youput in the setvar element replaces the variable in the statement. Thesetvar value must be a string, even numbers. Quoted strings are a bitof a problem, but someone came up with a brilliant way to handlethem. 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. Onesolution 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 theelegant 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 singleapostrophe defined in the SETVAR parameter - remember, to get asingle apostrophe you double up apostrophes within the opening andclosing apostrophes that indicate a character literal.

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

I recommend the Query Management Programming Guide, it's atwww.iseries.ibm.com/infocenter (pick any continent and release) inthe Database section at V5R3, in the Printed versions section or somesuch. Has good treatment of the quoted strings stuff. And a lot moreyou don't even want to know, esp. about forms. You are much betteroff 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 promptthe 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.