|
View previous topic :: View next topic
|
| Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
We generally SORT data based on its field type like CHARACTER, BINARY or NUMERIC. Is it possible to SORT data based on its length?
Example: Input
| Code: |
----+----1----+----2----+----3----+----4----+----5
LID1 KAMAKSHI JACK
LID9 BHAJARANGABALI JIVA
LID4 TREVOR ARNOLD
LID2 MANICKAVASAGANAICKER PETER
|
I want the SORT to happen based on 6th column.
Expected Output:
| Code: |
----+----1----+----2----+----3----+----4----+----5
LID4 TREVOR ARNOLD
LID1 KAMAKSHI JACK
LID9 BHAJARANGABALI JIVA
LID2 MANICKAVASAGANAICKER PETER
|
Please help.
Thanks. |
|
| Back to top |
|
 |
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1216
Location: Dublin, Ireland
|
|
|
|
What is the sixth column? We speak of fields in relation to records. You example shows three fields and the output seems to be sorted on the third field?
[/code] SORT FIELDS=(27,6,CH,A)
| Code: |
based on the length of the longest third field shown. Adjust the length for the longest field possible.
Garry. |
|
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1752
Location: Tirupur, India
|
|
|
|
| @Garry I think the TS is trying to sort based on the length of the second field. |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1216
Location: Dublin, Ireland
|
|
|
|
@Vasanth No, the 2nd field in required output is in the order T,K,B,M which is not sorted. however, the 3rd field in in the roder AR,JA,JI,PE, which is sorted. TS hasn't told us how long the field is, though.
Garry |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Maybe, we can't tell.
The order is on the LENGTH of the second field and the usual collating sequence for the third field.
Where columns come into it... or the 6th... |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1752
Location: Tirupur, India
|
|
|
|
I still think the sorting is based on the length of the 2nd field on input, which makes sense from TS initial post.
A crude attempt assuming there are no 9 in the second field,
| Code: |
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=WELLS.SORTIN,DISP=SHR
//SORTOUT DD DSN=WELLS.SORTOUT,DISP=OLD
//SYSIN DD *
SORT FIELDS=(6,20,CH,D)
INREC IFTHEN=(WHEN=INIT,
FINDREP=(STARTPOS=6,ENDPOS=25,IN=C' ',OUT=C'9')),
IFTHEN=(WHEN=INIT,
BUILD=(1,5,25,1,24,1,23,1,22,1,21,1,20,1,19,1,18,1,17,1,16,1,15,1,
14,1,13,1,12,1,11,1,10,1,9,1,8,1,7,1,6,1,26,55))
OUTREC IFTHEN=(WHEN=INIT,
FINDREP=(STARTPOS=6,ENDPOS=25,IN=C'9',OUT=C' ')),
IFTHEN=(WHEN=INIT,
BUILD=(1,5,25,1,24,1,23,1,22,1,21,1,20,1,19,1,18,1,17,1,16,1,15,1,
14,1,13,1,12,1,11,1,10,1,9,1,8,1,7,1,6,1,26,55))
/* |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| I was thinking to swap the bytes around in the field, last first through to first last, appending that key to the record. Sort on the generated key. Drop the generated key. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10903
Location: italy
|
|
|
|
very quick, very dirty POC a smarter solution might exist 
the jcl
| Code: |
****** ***************************** Top of Data ******************************
000001 //ENRICO1 JOB NOTIFY=&SYSUID,
000002 // MSGLEVEL=(1,1),CLASS=A,MSGCLASS=X
000003 //*
000004 //ICE1 EXEC PGM=SORT
000005 //SYSPRINT DD SYSOUT=*
000006 //SYSOUT DD SYSOUT=*
000007 //TOOLMSG DD SYSOUT=*
000008 //DFSMSG DD SYSOUT=*
000009 //SORTIN DD *
000010 FFFEFRFRFRRRFRF
000011 AA
000012 WWWWWW
000013 QQ
000014 REWQERERRE
000015 REER
000016 ERREERRE
000017 RQEQQQRRRERERRERR
000018 REREEE
000019 //SORTOUT DD SYSOUT=*,DCB=(RECFM=FB,LRECL=80)
000020 //SYSIN DD *
000021 OPTION EQUALS
000022 INREC IFTHEN=(WHEN=(01,20,CH,EQ,C' '),
000023 OVERLAY(71:C'00')),
000024 IFTHEN=(WHEN=(02,19,CH,EQ,C' '),
000025 OVERLAY(71:C'01')),
000026 IFTHEN=(WHEN=(03,18,CH,EQ,C' '),
000027 OVERLAY(71:C'02')),
000028 IFTHEN=(WHEN=(04,17,CH,EQ,C' '),
000029 OVERLAY(71:C'03')),
000030 IFTHEN=(WHEN=(05,16,CH,EQ,C' '),
000031 OVERLAY(71:C'04')),
000032 IFTHEN=(WHEN=(06,15,CH,EQ,C' '),
000033 OVERLAY(71:C'05')),
000034 IFTHEN=(WHEN=(07,14,CH,EQ,C' '),
000035 OVERLAY(71:C'06')),
000036 IFTHEN=(WHEN=(08,13,CH,EQ,C' '),
000037 OVERLAY(71:C'07')),
000038 IFTHEN=(WHEN=(09,12,CH,EQ,C' '),
000039 OVERLAY(71:C'08')),
000040 IFTHEN=(WHEN=(10,11,CH,EQ,C' '),
000041 OVERLAY(71:C'09')),
000042 IFTHEN=(WHEN=(11,2,CH,EQ,C' '),
000043 OVERLAY(71:C'10')),
000044 IFTHEN=(WHEN=(12,09,CH,EQ,C' '),
000045 OVERLAY(71:C'11')),
000046 IFTHEN=(WHEN=(13,08,CH,EQ,C' '),
000047 OVERLAY(71:C'12')),
000048 IFTHEN=(WHEN=(14,07,CH,EQ,C' '),
000049 OVERLAY(71:C'13')),
000050 IFTHEN=(WHEN=(15,06,CH,EQ,C' '),
000051 OVERLAY(71:C'14')),
000052 IFTHEN=(WHEN=(16,05,CH,EQ,C' '),
000053 OVERLAY(71:C'15')),
000054 IFTHEN=(WHEN=(17,04,CH,EQ,C' '),
000055 OVERLAY(71:C'16')),
000056 IFTHEN=(WHEN=(18,03,CH,EQ,C' '),
000057 OVERLAY(71:C'17')),
000058 IFTHEN=(WHEN=(19,02,CH,EQ,C' '),
000059 OVERLAY(71:C'18')),
000060 IFTHEN=(WHEN=(20,01,CH,EQ,C' '),
000061 OVERLAY(71:C'19')),
000062 IFTHEN=(WHEN=NONE,
000063 OVERLAY(71:C'20'))
000064 SORT FIELDS=(71,2,CH,A)
****** **************************** Bottom of Data **************************** |
the result
| Code: |
********************************* TOP OF DATA **********************************
AA 02
QQ 02
REER 04
WWWWWW 06
REREEE 06
ERREERRE 08
REWQERERRE 10
FFFEFRFRFRRRFRF 15
RQEQQQRRRERERRERR 17
******************************** BOTTOM OF DATA ********************************
|
I did not clean the output record, to show what was going on |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
SYMNAMES, of course. Save a lot of fiddling as well as being self-documenting.
To use, make correct start/len for ORIGINAL-KEY. Make enough of the OK-REV-nn,* fields. Define your ORIGINAL-RECORD correctly. Define GENERATED-KEY at the end of your fixed-length record or the beginning of your variable-lenghth record. OVERLAY-START, when coded like this, will automatically be at the same start position as the start of GENERATED-KEY. Job done. Oh, you can test it fully, of course.
| Code: |
//SORTLEN EXEC PGM=SORT
//SYMNOUT DD SYSOUT=*
//SYMNAMES DD *
ORIGINAL-KEY,1,5,CH
OK-REV-01,=,1,CH
OK-REV-02,*,1,CH
OK-REV-03,*,1,CH
OK-REV-04,*,1,CH
OK-REV-05,*,1,CH
ORIGINAL-RECORD,1,5,CH
GENERATED-KEY,10,5,CH !!! THESE TWO DEFINITIONS MUST BE CODED
OVERLAY-START,= !!! TOGETHER
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(GENERATED-KEY,A,ORIGINAL-KEY,A)
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(OVERLAY-START:OK-REV-05,
OK-REV-04,
OK-REV-03,
OK-REV-02,
OK-REV-01))
OUTREC BUILD=(ORIGINAL-RECORD)
//SORTIN DD *
ZZZZZ
AAAA
BBB
CC
D
EE
FFF
GGGG
99999
|
Output:
| Code: |
D
CC
EE
BBB
FFF
AAAA
GGGG
ZZZZZ
99999
|
In fact, it is so easy to change, I've done it:
| Code: |
//SORTLEN EXEC PGM=SORT
//SYMNOUT DD SYSOUT=*
//SYMNAMES DD *
ORIGINAL-KEY,5,20,CH
OK-REV-01,=,1,CH
OK-REV-02,*,1,CH
OK-REV-03,*,1,CH
OK-REV-04,*,1,CH
OK-REV-05,*,1,CH
OK-REV-06,*,1,CH
OK-REV-07,*,1,CH
OK-REV-08,*,1,CH
OK-REV-09,*,1,CH
OK-REV-10,*,1,CH
OK-REV-11,*,1,CH
OK-REV-12,*,1,CH
OK-REV-13,*,1,CH
OK-REV-14,*,1,CH
OK-REV-15,*,1,CH
OK-REV-16,*,1,CH
OK-REV-17,*,1,CH
OK-REV-18,*,1,CH
OK-REV-19,*,1,CH
OK-REV-20,*,1,CH
ORIGINAL-RECORD,1,33,CH
GENERATED-KEY,34,20,CH !!! THESE TWO DEFINITIONS MUST BE CODED
OVERLAY-START,= !!! TOGETHER
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(GENERATED-KEY,A,ORIGINAL-KEY,A)
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(OVERLAY-START:OK-REV-20,
OK-REV-19,
OK-REV-18,
OK-REV-17,
OK-REV-16,
OK-REV-15,
OK-REV-14,
OK-REV-13,
OK-REV-12,
OK-REV-11,
OK-REV-10,
OK-REV-09,
OK-REV-08,
OK-REV-07,
OK-REV-06,
OK-REV-05,
OK-REV-04,
OK-REV-03,
OK-REV-02,
OK-REV-01))
OUTREC BUILD=(ORIGINAL-RECORD)
//SORTIN DD *
0010 FFFEFRFRFRRRFRF
0011 AA
0012 WWWWWW
0013 QQ
0014 REWQERERRE
0015 REER
0016 ERREERRE
0017 RQEQQQRRRERERRERR
LID4 TREVOR ARNOLD
LID1 KAMAKSHI JACK
LID9 BHAJARANGABALI JIVA
LID2 MANICKAVASAGANAICKER PETER
|
Output:
| Code: |
0011 AA
0013 QQ
0015 REER
LID4 TREVOR ARNOLD
0012 WWWWWW
0016 ERREERRE
LID1 KAMAKSHI JACK
0014 REWQERERRE
LID9 BHAJARANGABALI JIVA
0010 FFFEFRFRFRRRFRF
0017 RQEQQQRRRERERRERR
LID2 MANICKAVASAGANAICKER PETER
|
|
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi,
here is another way
| Code: |
//SORT1 EXEC PGM=SORT
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD *
LID1 KAMAKSHI JACK
LID9 BHAJARANGABALI JIVA
LID4 TREVOR ARNOLD
LID2 MANICKAVASAGANAICKER PETER
//SYSIN DD *
INREC OVERLAY=(51:6,20,JFY=(SHIFT=RIGHT))
SORT FIELDS=(51,20,BI,A)
OUTREC BUILD=(1,50)
/*
|
Gerry |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
That's the winner Gerry. Less code. Only needs one sort key.
We could both have put EQUALS on. You never know when two MANICKAVASAGANAICKERs are going to turn up in your data. |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi Bill,
most installations have equals set up as the default, makes results less unpredictable but I take your point.
Gerry |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
