Re: how to use the Select Distinct

Vern,


I will say that while I love to participate, any topic created by op is ignored by myself due to the fact the question is completely open and usually ridiculous.


Jim





From: Vernon Hamberg
Sent: ‎Sunday‎, ‎June‎ ‎1‎, ‎2014 ‎11‎:‎46‎ ‎PM
To: Midrange Systems Technical Discussion





Oh well, I just decide to do what I do, and although I agree that more
information would be nice, I also have responded to this OP enough in
the past to have an idea, at least sufficient for me.

In fact, I asked him a question first.

We handle things differently, you and i - and that has to be OK, as we
are all different people.

I prefer not to get into too much around HOW people should ask, I guess
- as to whether any responses were "...useless banter...", I won't judge
that one.
.
And with that I'd best be quiet, methinks - I'm probably in too bad a
state to begin to be cordial!!

And I still wish this poster would reveal his true identity!!

Cheers
Vern

Perhaps we need a link in each post's list info to the "how to ask a
question" article - it has taught me a lot, but I'm too tired from a
weekend of pretty-much college-style all-nighter working to go looking -
mea culpa
On 6/1/2014 8:24 PM, CRPence wrote:

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.