>>>I also finally found the records should be sorted properly on key =
ON(28,8,CH) before you can use FIRST, LOWER, etc. Is it true the records
should be pre-sorted in this pass or previous pass for FIRST, LOWER, etc
to be working correctly?
Elardus Engelbrecht,
NO. The ON Fields on SELECT statement will be transformed to SORT FIELDS
and the file is SORTED on those fields. In your case ON(28,8,CH) is
translated as
SORT FIELDS=(28,8,CH,A)
MODS E35=(ICE35DU,12288)
However if your data is already sorted and in order then you can override
that SORT with OPTION COPY so that you don't have to resort the file once
again on the ON fields. I guess this is true in your case as seen from
your sample data.
Also you have an option of adding additional fields to the sorting
criteria.
For example you want to pick one record per key but also you want pick the
record that has the latest time.
Using your sample data.
//STEP0060 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
ABC007 13:49:11
ABC007 13:49:57
ABC008 12:38:29
ABC008 12:12:11
ABC008 12:28:51
ABC009 12:38:41
ABC010 13:48:51
ABC010 13:48:32
ABC010 12:31:57
ABC010 13:48:12
ABC011 13:50:12
ABC015 12:28:51
ABC015 12:16:11
ABC015 13:48:29
ABC015 12:29:05
----+----1----+----2----+----3----+----4----+----5----+----
ABC015 13:50:02
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,8,CH) FIRST USING(CTL1)
//*
//CTL1CNTL DD *
SORT FIELDS=(01,8,CH,A,
11,8,CH,D)
//*
As you can see I added the sorting on the TIME descending in CTL1 but the
SELECT FIRST will still operate on your ON field.
The output from this is
ABC007 13:49:57
ABC008 12:38:29
ABC009 12:38:41
ABC010 13:48:51
ABC011 13:50:12
ABC015 13:50:02
As you can see we picked the latest time record for each key. This trick
is extremely useful in situations where SORT FIELDS=(01,8,CH,A,11,8,CH,D)
and SUM FIELDS=NONE will NOT give you unique records as the combination of
2 fields is not eliminating duplicates.
So the more you explore DFSORT , you can find a wide range of uses.
>>More side notes: I tried out LOWER(2) just to see what happens, as you
also suggested, but my output got trimmed very heavily (as expected), so I
eventually dropped LOWER.
I Suggested using LOWER(2) as I did not know your real requirement and
guessed that you are only interested in having only UNIQUE records. With
Lower(2) any key which just occurs once will be picked, where as a key
which occured 3 or 4 times does not get picked as the number of keys is
higher than 2.
Once I knew what you are trying to do, I suggested using FIRST which gives
you the desired results.
Please feel free to send in your queries to our hotline or to me directly.
Thanks,
Kolusu
DFSORT Development
IBM Mainframe Discussion List <[email protected]> wrote on
04/23/2015 07:59:48 AM:
> From: Elardus Engelbrecht <[email protected]>
> To: [email protected]
> Date: 04/23/2015 08:00 AM
> Subject: Re: DFSORT / ICETOOL query about handling duplicats with
> SELECT HIGHER
> Sent by: IBM Mainframe Discussion List <[email protected]>
>
> Sri h Kolusu very kindly wrote:
>
> >As suspected you just need the first record per key.
>
> Yes. It is so for this specific subsetting reporting.
>
>
> >So I am not sure as to what you are trying to accomplish with
Higher(0).
>
> After my first attempts failed with countless variations of FIRST,
> FIRST(<value>), HIGHER, ALLDUPS, etc failed, I tried out HIGHER(0)
> to see what happens.
>
> Rewriting the paragraph, I assumed this (replacing x with 0):
>
> "HIGHER(0) - Limits the records selected to those with ON values
> that occur more than 0 times (value count > 0)."
>
> So, I tried value > 0 to get records which appeared more than 0,
> thus one or more times.
>
> (Side note - Now I'm wondering, why not value >= x instead value > x?)
>
>
> Eventually I see that I specified, in my first post, SORT FIELDS=
> (??,8,CH,A) in xxxCNTL and also ON(??,8,CH) in SELECT in TOOLIN DD.
> That was probably the main problem.
>
>
> >If your intention is to get only 1 record per key or get the first
> record from the duplicates, you can use FIRST or LOWER(2).
>
> I took your sample, rewrote my job from scratch and I got my desired
> results after comparing with ALLDUPS and NODUPS and also without
> handling duplicates.
>
> Your sample below is working!
>
> >//TOOLIN DD *
> > SELECT FROM(INOVER) TO(TEMP0001) ON(28,8,CH) FIRST USING(CTL1)
> > DISPLAY FROM(TEMP0001) LIST(PRINT) -
> ... etc ...
> >//CTL1CNTL DD *
> > INCLUDE COND=(05,10,CH,EQ,C'2015/04/21',AND,28,03,CH,EQ,C'ABC')
>
> Many thanks for your kind help.
>
> I also finally found the records should be sorted properly on key =
> ON(28,8,CH) before you can use FIRST, LOWER, etc. Is it true the
> records should be pre-sorted in this pass or previous pass for
> FIRST, LOWER, etc to be working correctly?
>
> More side notes: I tried out LOWER(2) just to see what happens, as
> you also suggested, but my output got trimmed very heavily (as
> expected), so I eventually dropped LOWER.
>
> Many thanks again for you kind help.
>
> Now, I'm SORTed out! ;-D
>
> Groete / Greetings
> Elardus Engelbrecht
>
> ----------------------------------------------------------------------
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to [email protected] with the message: INFO IBM-MAIN
>
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to [email protected] with the message: INFO IBM-MAIN
