Re: Query substitution variables and prompt screens; what's the better prompt screen -- MIDRANGE-L

Hi Dave

IQ is a 3rd-party product - not everyone is likely to have it.

Query/400 does not do this. However, it can be used to get *QMQRYs that have substitution variables (see below).

If you have Query Manager, you also have the SQL licensed product (5722-ST1), which gives you the interactive SQL screens. But you do not want every T, D, & H using that.

Query Manager is a front-end to query management, which you probably know from the mainframe. I think that's what QMF must be. But you don't want end users in Query Manager much, either.

The thing to use is what are called *QMQRY objects (query management queries). There's also a parallerl object called *QMFORM (query management form). A *QMQRY is simply a single SQL statement (any kind, just about) encapsulated in an object. It is executed with the STRQMQRY command. If you have substitution variables in the statement (Query Manager will not prompt if you do - it's a bit of a kluge), then the user will be prompted for them when running STRQMQRY. OR - this is the good sutff - you can set values for these variables right in the STRQMQRY command.

Your CL could actually use a DSPF to prompt the user, then run STRQMQRY with the values filled - nice combination of what you're thinking about.

Holler if you need more on this - I think QMQRYs are great.

There's another command that can execute SQL - RUNSQLSTM - but it cannot do SELECT statements.

You really need to look at the Query Management Programming book - the stuff on forms is a pain, but you can use Query Manager to handle that stuff. The good stuff is in the appendices and in how queries work. Then you might want to look at the Query Manager User Guide and example - all are in InfoCenter.

HTH
Vern

At 06:12 PM 6/14/2004, you wrote:

I have three query products on my iSeries probably like most of you.
They are Query for AS/400, DB2 Query Manager for AS/400 and IQ by New
Generation Software, Inc.    Whoever set up the "production" queries
here has the user entering a DFU session to enter variable data.  I've
never seen such a "junk" way to allow users to enter variables.   As you
probably know, DFU us invoked via a "driver" CL program and the user can
enter data although in a most clumsy and unforgiving manner.  The
results are written to a file.  the query is run from the same CL
program and it has to be constructed such that it queries the file that
DFU wrote plus any other files from which you need data and the input to
the comparison is from the file written to by DFU.  There must be a
better way.

In my mainframe days there was QMF for querying DB2 and it was easy to
enter a substitute parm in the query and, automatically, when the user
ran the query interactively they would be prompted for variable(s) for
use in the underlying query.  No separate "DFU" required.  Do each of
the above query products work the same?  If so, how.  I have not been
able to find any good documentation that speaks to what I'm trying to
do.

If I must write to a file because the iSeries query products don't have
as good an automatic screen prompt as mainframe QMF (and other
products), then I am thinking about using REXX to call a web browser and
display nicely formatted screens to allow the user to enter his
variables.  Then the REXX could be used like CL to invoke the query
product.

Any ideas?

Thanks in advance,

Dave Odom