× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.