Re: take 2: how to sort a file in place -- MIDRANGE-L

Hi Chuck

What you describe is basically how I thought STRQMQRY worked - as you say, it doesn't get away from making a copy - it is just a single step instead of more than one.

I've always assumed it was putting the result set somewhere in a temp location, then writing that back to the original table. The fact that it does this under the covers is why I consider this to be a one-step kind of thing - as the user sees it - low-level implementation isn't that important to me here.

Now as to will this behavior change? I suspect not, unless someone REALLY gets nervous that absolutely no notification is given that the original contents will be replaced. My discovery of this behavior, 10 or 15 years ago, does make me very nervous, but given careful practices like backups and the like, it is a very useful technique in my estimation, to be used with extreme care!!

Vern

On 1/24/2013 1:14 PM, CRPence wrote:

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"
(OUTMBR).

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].

Regards, Chuck

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
in WORKFILE1?

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?