Re: how to use the Select Distinct

On 01-Jun-2014 19:11 -0500, Vernon Hamberg wrote:

On 6/1/2014 7:06 PM, CRPence wrote:

On 01-Jun-2014 18:02 -0500, Hoteltravelfundotcom wrote:

Can u show me how to use Selecting distinct records in a CL?

I suppose u could do that, whoever u is; maybe you could ask u in
a direct email instead of a public forum of IBM i professionals
;-)

Otherwise, probably best to tell the reader what is the definition
of each of 'selecting', 'distinct', and 'in a CL', instead of
depending on them to intuit your intentions. As a "Control
Language", probably the easiest would be to invoke a program to
perform exactly whatever is desired; for example, if the data
/records/ are in a database file, then perhaps invoking one of
STRQMQRY, RUNSQL, QSH CMD('DB2 ...'), RUNQRY, RCVF, OPNQRYF, or
etc..

Another question for me is, does this person want to read the results
in CL? I do know this guy is a 400 person, so I can * will make some
assumptions.

Why *assume*, when the scenario could and should be properly described? That was my point, that the OP was wasting time of the readers. Why should many readers waste their time to try to guess or assume what the writer intends? The OP should clarify their scenario.

If someone had asked how to /drive fast/ we might be able to answer "use the accelerator". Ask how to /select distinct data using CL/ and the answer could be "invoke a program." Either reply could be honest or facetious, yet both probably are unlikely to answer the question that the inquirer intended to ask.

Here we have added three followups in this thread with useless banter, probably effecting no progress whatsoever with regard to resolving the poorly defined\described requirements.

Consider... With the term "records", we do not even know if the data /records/ are [delimited] stream or database. Does /distinct/ intend to imply across some specific keys or that each row is entirely distinct from another?

Others who ask this kind of thing in the past want to use OPNQRYF to
do the same as select distinct fields from file.

Perhaps, but I think maybe you mean Receive File (RCVF)? Using OPNQRYF as a means to create a shared Open Data Path (ODP) that can then be used by the CLP to /read/ the records is possible, using RCVF. However any Output File (OUTFILE) support that performs whatever effects the desired /distinct/ records enables the same; i.e. enables using RCVF. The only difference is the ability of the OPNQRYF to provide an ODP as the effective logical view of the data instead of a physical copy; of course the Copy From Query File (CPYFRMQRYF) similarly could be used to create a physical copy if that extra step were desirable.

Some have suggested using OPNQRYF is possible, I think, though, that
it would require that all the fields be the key - you know OQF better
than I.

That is probably how I would accomplish a row-distinct processing with the OPNQRYF. However if there is any keyed access path, the Open Query File (OPNQRYF) request could use that key [similarly with UNIQUEKEY(*ALL)] and defer the remainder of distinct processing to the CLP as reader of the records. Of course with any data ordering, even without unique key processing, the CLP can perform distinct processing for each row received with RCVF.

Otherwise, one would have to get Bruce Vining's product to make this
work - I don't about anything to allow direct reads of query output
in CL.

Why a product? Just call any program; no need to buy a program, just write a program [in a language more appropriate to the task] to do the work, or invoke an existing program. Any program could create an effective Output File that the CLP then can read with RCVF, without the CL processing having to perform whatever logic is required for the desired /distinct/ processing. I do not recall, but the Format Data (FMTDTA) command might even provide some distinct row capability.