Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sorting rows and columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Aug 18, 2011 11:58 am    Post subject: Sorting rows and columns
Reply with quote

Hi,

I have a requirement like this


To sort records which are in input like this say record length of 200

Input file

C;345;000;987
A;123;456;345
B;234;123;789

Output file

A;123;345;456
B;123;234;789
C;000;345;987

Not sure if It could be done using sort and I searched forum but couldnt find something like my requirement

Any guidance will be helpful
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Aug 18, 2011 12:18 pm    Post subject:
Reply with quote

As the records in your output file do not match the records in the input file can you please explain the rules for getting from input to output?

Also provide all the other necessary information which you know is required and use code tags. You have been a member for 5 years so your diligent reading of the forum over the years would tell you what this information is and how to use code tags.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Aug 18, 2011 1:03 pm    Post subject:
Reply with quote

Apologies about that I usually use code tags missed out here icon_redface.gif

Rules are

1.I need sort the rows based on 1st byte in Ascending
2.The columns here the first number indicates starting point and the next number indicates length (3,3) (7,3) (11,3) should be sorted ascending for every record ie

Code:

C;345;000;987
A;123;456;345
B;234;123;789



should become

Code:


A;123;345;456
B;123;234;789
C;000;345;987

Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 1:21 pm    Post subject:
Reply with quote

your explanation makes no sense about starting point and length,

anyway, each record contains 1 key (1,1) and 3 semicolon delimited fields,
each of 3 bytes.??? correct so far??


so each record needs to be broken down into prim-key (1,1) plus one of the secondary fields:
a 123
a 456
a 345

and then assembled as:
a;123,345;456

and then the reassembled file needs to be sorted on 1,1.

you have been around long enough to do this with a two part ICETOOl step.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Aug 18, 2011 1:46 pm    Post subject:
Reply with quote

Hi,
this might assist
Code:

//STEP0001 EXEC  PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//SYSOUT   DD SYSOUT=*                                                 
//INFILE   DD *                                                         
C;345;000;987                                                           
A;123;456;345                                                           
B;234;123;789                                                           
//OUTFIL1  DD DSN=&&T1,                                                 
//            DISP=(,PASS,DELETE),                                       
//            UNIT=SYSDA,                                               
//            SPACE=(TRK,(5,5),RLSE)                                         
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
 COPY FROM(INFILE) USING(CTL1)                                         
 SELECT FROM(OUTFIL1) TO(OUT) ON(1,1,CH) FIRST USING(CTL2)             
/*                                                                     
//CTL1CNTL DD *                                                         
  OUTFIL FNAMES=OUTFIL1,                                               
   BUILD=(1,80,/,                                                       
          1,6,11,3,6,4,/,                 
          1,2,7,4,11,3,2,4,/,             
          1,2,7,4,3,4,11,3,/,             
          1,2,11,3,2,4,6,4,/,             
          1,2,11,3,6,5,3,3)               
/*                                       
//CTL2CNTL DD *                           
  SORT FIELDS=(1,80,BI,A)                 
/*



Gerry
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 3:10 pm    Post subject:
Reply with quote

Gerry,

different approach, works well. good thinking.

by the way, still haven't figured out how to do it using my suggestion.
splitting them up is easy,
putting them together in one step still eludes me
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Thu Aug 18, 2011 3:33 pm    Post subject:
Reply with quote

Hi Dick,

I believe you could try the RESIZE function but I'm still on ICE201I E level of DFSORT and many new functions are unavailable to me to test them out.


Gerry
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Aug 18, 2011 3:47 pm    Post subject:
Reply with quote

gcicchet wrote:
Hi Dick,

I believe you could try the RESIZE function but I'm still on ICE201I E level of DFSORT and many new functions are unavailable to me to test them out.


Gerry

Yes I also was on same thought...
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Aug 18, 2011 5:07 pm    Post subject:
Reply with quote

something like....
Code:

//STEP0100 EXEC PGM=ICETOOL                                     
//SYSOUT   DD SYSOUT=*                                         
//SIN1     DD *                                                 
C;345;000;987                                                   
A;123;456;345                                                   
B;234;123;789                                                   
//SIN2     DD DSN=&&TEMP,DISP=(MOD,PASS)                       
//SOUT     DD DSN=OUTPUT.DATASET.NAME,DISP=(NEW,CATLG),LRECL=13,
//            RECFM=FB                                         
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//TOOLIN   DD *                                                 
  COPY FROM(SIN1) TO(SIN2) USING(CTL1)                         
  RESIZE FROM(SIN2) TO(SOUT) TOLEN(15) USING(CTL2)             
//CTL1CNTL DD *                                                 
  OUTFIL BUILD=(1,5,/,1,2,7,3,/,1,2,11,3)                       
//CTL2CNTL DD *                                                 
  SORT FIELDS=(1,5,CH,A)                                       
  OUTFIL BUILD=(1,5,7,4,12,4)                                   
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 5:16 pm    Post subject:
Reply with quote

well,
i fought with splice, did not try resize.
problem with splice is i needed a two command icetool.

so, I fought with IFTHEN
(have been avoiding this for 5 months)
and created a 1 step sort
without the need to add extra records.

Code:

//STEP010  EXEC PGM=SORT
//TOOLMSG  DD  SYSOUT=*
//DFSMSG   DD  SYSOUT=*
//SORTOUT  DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//SORTIN   DD  *
E;123;456;789           1,2,3
A;123;456;345           1,3,2
C;345;000;987           2,1,3
B;234;789;123           2,3,1
F;987;123;456           3,1,2
D;987;654;123           3,2,1
//SYSIN    DD  *
  SORT FIELDS=(1,12,CH,A)
  INREC IFTHEN=(WHEN=(3,3,CH,GT,7,3,CH),
          BUILD=(1,2,7,4,3,4,11,3),HIT=NEXT),
        IFTHEN=(WHEN=(7,3,CH,GT,11,3,CH),
          BUILD=(1,6,11,3,6,4),HIT=NEXT),
        IFTHEN=(WHEN=(3,3,CH,GT,11,3,CH),
          BUILD=(1,2,11,3,6,4,3,3),HIT=NEXT),
        IFTHEN=(WHEN=(3,3,CH,GT,7,3,CH),
          BUILD=(1,2,7,4,3,4,11,3))
/*


output is:
Code:

A;123;345;456
B;123;234;789
C;000;345;987
D;123;654;987
E;123;456;789           1,2,3
F;123;456;987


couple of notes:

the numbers in columns 25 - 29 of the SORTIN
are my notes for creating all permutations of a 3 item sequence
(3 X 2 X 1 = 6
1 is smallest
2 is middle
3 is largest)
so to test my logic i needed the correct 6 input test cases.
though I only needed 4 IFTHEN's
If there had been 4 sequences, then there would have been 24 test cases
and ? IFTHEN's, (5? 4 to bubble thru and 1 for the 1st?)
in essense it is a column-wise bubble sort within a record sort.

the reason 1,2,3 is in the 'E' record of the output
is that none of the IFTHEN's were a hit for this record.

if you can't tell, I am patting myself on the back to the point of muscle strain.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 5:25 pm    Post subject:
Reply with quote

Escapa,

thx for the RESIZE example.

by the way,
using your solution
with my input,
still have a 1,2,3 on the E record.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 5:27 pm    Post subject:
Reply with quote

premkrishnan,

so now you have 3 solutions.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Aug 18, 2011 5:28 pm    Post subject:
Reply with quote

Thanks all.I was trying out something near to Escapa but couldnt achieve it .

RESIZE example is really helpful.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 18, 2011 7:09 pm    Post subject:
Reply with quote

premkrishnan wrote:
RESIZE example is really helpful.


takes more resources than mine. icon_lol.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Stripping and Sorting of VB file G SRINIVASA RAO SYNCSORT 5 Wed Sep 14, 2016 1:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us