Query Prompt

[Tony Myers]

I have a query that I want users to be able to enter their own date
parameters through a date prompt when they select the query via menu option.
Our users are not trained in query and I do not want to train them in query
since there are so many of them. I don't want them to get a select records
screen. I just need a From and To date prompt.

TIA
Tony Myers

[Jim Kauffman]

Use the qmqry function to allow prompted data to be pulled into an existing
query.

The following is an excerpt from the now defunct Midrange Guru newsletter of
the old Midrange Computing Magazine:

<<<<<<<<From Midrange Guru at Midrange Computing>>>>>>>>>>>
Query Management comes with OS/400. That is, you don't have to license it
separately. It is often confused with Query Manager, which works with Query
Management and costs you extra money. Query Management (hereafter
abbreviated QM) queries are built from two source members: a query
definition, which is an SQL statement, and a report form. It's not
impossible to build these source members from scratch, but I don't advise
you to try to do so. You could probably handle the SQL statement, but trying
to write the report format language would be challenging, to say the least!
Instead, you can build these members from Query/400 queries.
To begin, I assume you have a Query/400 query that you would like to run
after prompting for record selection criteria. In this example, I call the
query JOIN0B1. First, run the Analyze Query (ANZQRY) command against the
query. The system may respond with diagnostic messages telling you of
problems that need to be fixed before you convert the query. For instance,
ANZQRY may tell you that QM will apply a different type of editing to a
field.

Next, create a source physical file to contain the QM query member. It
should have a record length of 91 bytes, which allows for an SQL statement
of 79 characters. The standard name for the file is QQMQRYSRC:

CRTSRCPF FILE(xxx/QQMQRYSRC) RCDLEN(91)

Create another source physical file for the report form. IBM's standard is
QQMFORMSRC:

CRTSRCPF FILE(QQMFORMSRC)

Now, use the Retrieve Query Management Query (RTVQMQRY) and Retrieve Query
Management Form (RTVQMFORM) commands to convert the Query/400 query to QM:

RTVQMQRY QMQRY(xxx/JOIN0B1) +
SRCFILE(xxx/QQMQRYSRC) ALWQRYDFN(*ONLY)
RTVQMFORM QMFORM(xxx/JOIN0B1) +
SRCFILE(xxx/QQMFORMSRC) ALWQRYDFN(*ONLY)

The two source members have the same name as the Query/400 query. You can
use other names if you wish by filling in the SRCMBR parameter of these
commands. By the way, you can run these commands from options 5 and 6 of
menu CMDQM.

Now, you have two source members that can be used to generate a QM query
like the Query/400 query you're replacing. Edit the source member containing
the SQL command. You will see the record selection criteria in the WHERE
clause, like this:

WHERE VENDORID = T03.ID
AND BUYERID = T04.ID
AND( T02.DUEDATE = 20000301)

Change the hard-coded value to a placeholder, which is a string of up to 18
characters beginning with an ampersand (&):

WHERE VENDORID = T03.ID
AND BUYERID = T04.ID
AND( T02.DUEDATE = &DUEDATE)

Create the query and the query form:

CRTQMQRY QMQRY(xxx/JOIN0B1) +
SRCFILE(xxx/QQMQRYSRC)
CRTQMFORM QMFORM(xxx/JOIN0B1) +
SRCFILE(xxx/QQMFORMSRC)

Now, you have two new objects called JOIN0B1. One is of type *QMQRY, the
other of type *QMFORM. Use the Start Query Management Query (STRQMQRY)
command to run the query. Place the substitution value in the SETVAR
parameter:

STRQMQRY QMQRY(xxx/JOIN0B1) +
OUTPUT(*PRINT) +
QMFORM(xxx/JOIN0B1) +
SETVAR((DUEDATE 20000401))

Notice that the placeholder name is not preceded by an ampersand.

If the field against which you are selecting records is a character field,
you will see a quoted value in the WHERE clause:

AND( BUYERID = 'BAL')

Replace all of the value, including quotes, with a placeholder:

AND( BUYERID = &BUYER)

At runtime, enclose the value in triple quotes:

STRQMQRY QMQRY(xxx/JOIN0B2) +
OUTPUT(*PRINT) +
QMFORM(xxx/JOIN0B2) +
SETVAR((BUYER '''BAL'''))

Reports are written to printer file QSYS/QPQXPRTF, which has a line length
of 80 characters. Use the OVRPRTF command to specify a wider line length
and/or other printer settings. If you prefer, you can use the Change Printer
File (CHGPRTF) command to change to a wider line length, but you'll probably
have to make that change every time you install a new release. To learn more
about Query Management, see Mike Faust's article "Query Manager Made
Simple," (Midrange Computing magazine, June 2001) and Sharon Cannon's
article "Query Management Forms" (MC, March 1995).

----------------------------------------------------------------------------
----

"Tony Myers" <f...@mosquitonet.com> wrote in message
news:u1ht3o3...@corp.supernews.com...

[Dave]

I stuck a fish in my ear and understood "Tony Myers"
<f...@mosquitonet.com> to say:

You can find a good source here:
http://faq.midrange.com/index.pl?_highlightWords=query%20prompt&file=106

Dave

[Francis Lapeyre]

You can create a command to do that; the CPP for the command can do an OPNQRYF on the file that the query uses. I have done it; it does work.

--

Regards,
Francis Lapeyre

"Facilius est camelum per foramen acus transire quam divitem intrare in regnum Dei."
(Mk 10:25)

Return address is filtered via SpamCop.

"Tony Myers" <f...@mosquitonet.com> wrote in message news:u1ht3o3...@corp.supernews.com...

[Tony Myers]

Thanks all for your help.
Dave - excuse my density, I don't get the fish thing. Hope you have q-tips.
;o)

"Tony Myers" <f...@mosquitonet.com> wrote in message
news:u1ht3o3...@corp.supernews.com...