For the STRQMQRY Output File support to be able to effect replacement
of the member being queried, that feature must implement using a cursor
for which FETCH has been done until EOF and been closed; i.e. the data
has been written somewhere [IIRC to a temporary file named QQXTEMP## in
QTEMP]. Using that feature also depends on the rows being selected from
the TABLE using WITH NC [though I recall that is the default, and that a
STRQMQRY does not participate in a previously started STRCMTCTL nor have
a COMMIT parm], because the SAVE DATA AS with the replacement option
[had always] used the CLRPFM to effect the clear for the request to
*REPLACE on the "Replace or add records" on the "Output member options"
For the lack of an error message QRY1034 to diagnose a restriction on
the output database file member being the same as an input database file
member, one might safely presume the QMQRY [implemented using a file
QTEMP/QQXTEMP## to save the result set] for its OUTFILE support will
indeed effect what is described. However, I am not aware of any
documentation that explicitly states that effect; best to find the doc
than to merely assume. Without that assurance, some queries could
continue running indefinitely, dependent upon the implementation.
So anyhow, the STRQMQRY is not really avoiding a temporary copy of
the data, merely removing a requirement to do so explicitly, and the
ability of STRQMQRY to effect replace of the file named on the FROM
clause *may* depend upon an implementation detail [subject to change; so
some assurance that is a documented effect may be worthwhile].
On 23 Jan 2013 12:18, Vernon Hamberg wrote:
Actually using the DOCSQL command, this can be done in place - that
command is part of an RJS product they have, right, Joel? It is
basically the same as the various EXCSQL or RUNSQL commands that have
used QMQRY techniques.
All he has to do is run the SELECT with a distinct and ba-da-boom --
oops, boom doesn't sound so good!! No, really, just direct output back
to the file being queried and all is done.
In general, one could create a QMQRY object with a statement like SELECT
DISTINCT FILE.* from LIB/FILE ORDER BY WHATEVER, then run STRQMQRY and
set the OUTPUT and OUTFILE parameters as suggested. Using the product
command is just an abstraction-level easier.
On 1/23/2013 1:28 PM, rob@xxxxxxxxx wrote:
For that, you can't do it in place. Use the work file.
On 23 Jan 2013 11:25, Stone, Joel wrote:
OK allow me to explain:
I was tasked with sorting AND performing data reduction on a work
file, then passing to another developer.
I would like to sort WORKFILE1 by CUST# and ITEM#, and eliminate
duplicate CUST# + ITEM# combinations.
Can this be done in one SQL statement with the result placed back
Or should I create a new table QTEMP/WORKFILE2 using DISTINCT or
GROUP BY, and then CPYF back to WORKFILE1.
Or is there a totally different and better method to accomplish?
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact