Re: take 2: how to sort a file in place

[CRPence <CRPbottle@xxxxxxxxx>]

  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?