On 30-Jun-2016 09:19 -0500, Steinmetz, Paul wrote:
I have an AS/400 query with a hardcoded date.
The date is today's date - 6 months.
If the intended effect is always "today's date - 6 months", then
revise the expression to use the CURRENT(DATE) - 6 MONTHS as the expression.
Example 1160130.
Not sure I understand the example. In Query/400, a hard-coded date,
a date literal, that presumably must be coded as DATE('2016-01-30')?
I'd like to make this date a variable.
Query/400 has no direct support for variables; i.e. no means to
provide a value for any variables that are defined [since the loss of
OfficeVision that took advantage of the capability to define variables
in the Query Definition (QRYDFN)]. The means to provide replacement
values [be that direct replacement for literals or literals specified in
place of /variable/ names] was only made available via the Run-Time
Record Selection (RCDSLT) feature of the Run Query (RUNQRY) command.
Can this be done within the query?
Instead of a variable, define a file in the query, and then the join
criteria to get the value from the field of [the one row of] that file.
Then precede the RUNQRY with an update to the file to set the value(s)
in the file being joined; e.g. use the Run SQL Statements (RUNSQL) to
issue an UPDATE, or for a new\replaced file, to issue an INSERT.
Or
Must it be done outside the query and pass the date as a variable to
the query?
Then, instead of calling RUNQRY, I would need to STRQMQRY.
The Start Query Management Query (STRQMQRY) command can successfully
run some Query/400 Query Definitions, as a converted QM Query [see
Analyze Query (ANZQRY) and Retrieve QM Query (RTVQMQRY)] using the Allow
Query Definition (ALWQRYDFN) parameter specification, and even use the
Set Variables (SETVAR) parameter to assign values to any variables
defined in the Query/400 QRYDFN. Noting again however, that what is run
is *not* the query definition; i.e. a separate LPP, the QM feature, will
be running and producing the output, and the effects must be understood
and evaluated to ensure they meet expectations.
Any thoughts from the group?
Depends on what are the requirements to be met.
If the output [type; file, displayed report, printed report] from the
STRQMQRY is acceptable, then I might use either the STRQMQRY The_QRYDFN
ALWQRYDFN(*ONLY) SETVAR(...) or more likely [for reporting] use STRQMQRY
The_QMQRY ALWQRYDFN(*NO) SETVAR(...) QMFORM(...) after creating
the_QMQRY object using Create Query Management Query (CRTQMQRY) from the
retrieved query [or from a newly designed query and QM Report Form
(QMFORM) from the Start QM (STRQM) interactive query definer].
If the goal does not include reporting, e.g. the output of the QRYDFN
is intended solely for Output File (OUTFILE), then I might instead just
code the SQL in a CLP or REXX and run that SQL with an interpreter like
one of QSH [DB2 command], RUNSQL, STRREXPRC, or something else.
midrange.com
