Q: We run queries weekly using an end-of-week date. Is there a way to have a Query/400 query prompt the user to enter an end-of-week date instead of having the IS department change the query each week to specify the end-of-week date?
A: Query/400 itself won't prompt for an end-of-week date, but here are some ideas for solving your problem.
Query/400 supports joins, and you can use this support to your advantage in "passing" parametric information to a query. For example, you can write a program that prompts for the end-of-week date and writes the information to a database file and then use this database file in a join clause in the query. You needn't worry about creating legitimate fields for your join criteria. Just specify a "dummy" join, such as joining the files with a Not Equal condition with fields you know will never be equal. You'll then be able to reference the end-of-week date field from the database file you created.
Another option is to use a dependent query (documented in DB2 for AS/400 Query Management Programming, SC41-5703) to pass an end-of-week date. There are some considerations, though. You can't run a dependent query directly. However, you can use the STRQMQRY (Start Query Management Query) command to run it. When you use STRQMQRY, though, you're actually running a Query Management (QM) query rather than a Query/400 query, so the rules and output format for QM queries apply.
To define a dependent query using your end-of-week date, you must enter a variable in the Select Records definition. You do this by preceding a field name with a colon (:). For example, you can use the variable ":SELDAT" (selection date) in your comparison. Query/400 will want know the source of this variable and will prompt you for that information. You can simply specify a nonexistent query as the source because it will never be used. When you run this query with STRQMQRY, specify ALWQRYDFN(*YES), and the system will automatically create a QM query from the Query/400 definition.
The STRQMQRY command's SETVAR parameter lets you set the value for variables the query uses. If you don't specify this value, the system prompts you for the value at runtime. I suggest you always use a program that issues the STRQMQRY command rather than letting users run the command directly to prevent problems that will occur should a user enter the parameter value incorrectly. Users probably won't know the data type (e.g., character, numeric) and therefore won't know whether they need to simply type the value or enclose it in quotation marks.
Figure 3a shows a sample CL program that passes a selection date in numeric format to a query. Figure 3b shows the same example using a character format date. You'll need to write only front-end code to prompt for the date.
— Gary Guthrie