Re: AS/400 query with date - 6 months

On 30-Jun-2016 09:47 -0500, Carel wrote:

In a Query/400 QRYDFN you need to have a variable starting with a
colon (:INPUT)

With an intent to clarify, for the archives:

Variables [originally, field names defining a "Dependent Values" relationship] *can be defined* in the QRYDFN. Yet one must understand there is *no capability* for use of the variable via the Query/400 reporting features; nothing available via neither the Run Query (RUNQRY) nor when run with either of option 9=Run or 8=Submit from the Work With Queries (WRKQRY), to assign a value to a variable that was defined within a QRYDFN.

Values *can be assigned* to the variables defined in a QRYDFN, only via the interface to OfficeVision (OV) and by use of the Start QM Query (STRQMQRY) with an appropriate Allow Query Definition (ALWQRYDFN) specification. However, neither of those interfaces is a reporting feature of the Query/400, and the latter does not even use the Query/400 run-time. The *distinction is important*, as the QM Query feature can be quite different from Query/400; especially problematic, if one _assumes_ a QRYDFN that can be run via STRQMQRY will both produce the same result set and will produce a functionally equivalent report -- in many cases, the effects can be "quite different" as I just noted, but consider that the Analyze Query (ANZQRY) should be used help identify some issues\differences that should be expected or as reasons to create an actual QMQRY instead of using the QM feature to refer to a QRYDFN.

Using Query Manager you need to use an ampersand and capitals
(&INPUT)

So for example, the following query defined as a QMQRY:

select * from The_File where The_Character_Fld >= &INPUT

If it is a character string, the value should be in double quotes.
(''1160101")

I think the reference made above, is to the use of the Set Variables (SETVAR) of STRQMQRY, and the intention was to suggest the use of escaped [doubled] apostrophes [what was shown mixes the use of doubled apostrophes and a double-quote character which would be syntactically incorrect for the SETVAR]. Presumably, this is what was intended to suggest how to pass the character string; the first apostrophe [in each set of three] delimiting the string for the command interpreter, and the second apostrophe [in each set of three] as the escape character, and the third apostrophe as the delimiter for the string that is passed to the QM query for concatenating into the dynamic SQL:

SETVAR((INPUT '''1160101'''))

Combining the above query string and the effect of SETVAR is the following string, as generated by the QM feature, that will be sent to be interpreted and executed by the SQL:

select * from The_File where The_Character_Fld = '1160101'