IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sort two file having same field, one fields Less than other.


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
himanshu malik

New User


Joined: 01 Feb 2017
Posts: 4
Location: India

PostPosted: Thu Feb 02, 2017 10:09 am
Reply with quote

I have two data sets with same key which will be used in Joinkey, but I need a condition where a same field present in both files can be compared as less than, so that output will only have records from 2nd file where the same field is less than other.

Eg

Data Set A
Code:
AAAAA   111
BBBBB   222
CCCCC   333

Data Set B
Code:
BBBBB    100
CCCCC    223
DDDDD    300


Output
Code:
BBBBB   100


Cond:1 Key will match, that can be handled by join key.
Cond 2 : 2nd field in data set B < 2nd field in data set A. How can this be handled.

Regards

coded
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 88
Location: India

PostPosted: Thu Feb 02, 2017 11:18 am
Reply with quote

Hello,

himanshu malik wrote:
...
Cond:1 Key will match, that can be handled by join key.
Cond 2 : 2nd field in File B < 2nd field in File A. How can this be handled.
...


Once the records have been JOINed and REFORMATted, use INCLUDE function of OUTFIL (provided you're using it; since you haven't shown your JOINKEYS) to test whether the column 'n' from DS-A is LT or GT the column 'm' of DS-B.
Note that at this point, if the records from the two DS match, the joined records will all lie on the same row hence can be easily tested using INCLUDE/OMIT features of DFSORT.

Hope this helps.
Back to top
View user's profile Send private message
himanshu malik

New User


Joined: 01 Feb 2017
Posts: 4
Location: India

PostPosted: Thu Feb 02, 2017 11:28 am
Reply with quote

Please find my Sort step.

Code:
//P07DE11 EXEC  PGM=SORT,COND=(0,NE)                             
//SYSOUT   DD  SYSOUT=*                                           
//SYSPRINT DD  SYSOUT=*                                           
//SORTJNF1 DD  DSN=Data set A
//SORTJNF2 DD  DSN=Data set B
//AMBSK    DD  DSN=Data set C,         
//             DISP=(NEW,CATLG,DELETE),                           
//             SPACE=(CYL,(850,85),RLSE),                         
//             LRECL=25,RECFM=FB,                                 
//             DSORG=PS                                           
//SYSIN    DD  *                                                 
   JOINKEYS FILE=F1,FIELDS=(1,5,ZD,A)                           
   JOINKEYS FILE=F2,FIELDS=(1,4,ZD,A)                           
   REFORMAT FIELDS=(F2:1,25)                                     
  SORTFIELDS=COPY                                                                               
   OUTFIL FNAMES=AMBSK                                           
//*                                       


Here what include condition can I use to check for 2nd field.

Note : I am just a starter in SORT.

Regards
coded
Back to top
View user's profile Send private message
himanshu malik

New User


Joined: 01 Feb 2017
Posts: 4
Location: India

PostPosted: Thu Feb 02, 2017 11:30 am
Reply with quote

Correction : JOINKEYS FILE=F2,FIELDS=(1,5,ZD,A)
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 88
Location: India

PostPosted: Thu Feb 02, 2017 1:17 pm
Reply with quote

Hello,

There are various errors in your code.

Refer the below code piece, study every step/keyword, consult the DFSORT programming guide to understand why it is coded the way it is, so that you can program it in future:

Code:

//SORTJNF1 DD *                     
AAAAA 111                           
BBBBB 222                           
CCCCC 333                           
/*                                 
//SORTJNF2 DD *                     
BBBBB 100                           
CCCCC 223                           
DDDDD 300                           
/*                                 
//SORTOUT  DD SYSOUT=*             
//SYSIN DD *                       
*                                   
* SETUP THE KEYS FOR DS-A           
*                                   
 JOINKEYS FILE=F1,FIELDS=(1,5,A)   
*                                   
* SETUP THE KEYS FOR DS-B           
*                                   
 JOINKEYS FILE=F2,FIELDS=(1,5,A)   
*                                   
* SINCE REQUIREMENT IS OF ONLY PAIRED RECORDS, WHICH IS THE DEFAULT FOR
* JOINKEYS, HENCE THE JOIN STATEMENT IS SKIPPED. SINCE A COMPARISON IS       
* REQUIRED AT A LATER STAGE, HENCE THE COMPLETE RECORD OF BOTH DS IS   
* TAKEN IN REFORMAT FIELDS.                                             
*                                                                       
 REFORMAT FIELDS=(F1:1,10,F2:1,10,?)                                   
*                                                                       
* AT THIS POINT ONLY THE MATCHED RECORDS HAVE BEEN SELECTED, AND HAVE   
* BEEN ARRANGED IN THE SAME ROW. SO WE CAN TEST THEM AS PER OUR         
* REQUIREMENT. SINCE THE REFORMATTED RECORDS ARE A COMBINATION OF       
* RECORDS FROM BOTH DATASETS A AND B, USE BUILD TO SELECT ONLY DS-B     
* DATA.                                                                 
*                                                                       
 OUTFIL INCLUDE=(17,3,ZD,LT,7,3,ZD),                                   
        BUILD=(11,10)                                                   
*                                                                       
* AT THIS POINT WE HAVE ALL THAT IS NEEDED, I.E. FILTERED DATA IN THE   
* PRECISE FORMAT THAT WE NEEDED IT TO BE IN, SO A SIMPLE COPY TO COPY   
* THE RECORDS TO OUTPUT DATASET.                                       
*                                                                       
 SORT FIELDS=COPY                                                       
/*


Output:

Code:

BBBBB 100
CCCCC 223
Back to top
View user's profile Send private message
himanshu malik

New User


Joined: 01 Feb 2017
Posts: 4
Location: India

PostPosted: Thu Feb 02, 2017 2:48 pm
Reply with quote

Hi,
Required output should be

BBBBB 100

CCCCC 223 should not be in output file since 223 is not LT 222.

Can you please let me know the importance of BUILD=(11,10), being used in above code.

Code:
   JOINKEYS FILE=F1,FIELDS=(1,5,ZD,A)   
   JOINKEYS FILE=F2,FIELDS=(1,5,ZD,A)   
   REFORMAT FIELDS=(F1:1,10)             
   OUTFIL INCLUDE=(7,3,ZD,LT,7,3,ZD),   
           BUILD=(11,10)                 
   SORT FIELDS=COPY                       
//*                           


This did not worked for me. Any error in above code.

coded
Back to top
View user's profile Send private message
Abid Hasan

New User


Joined: 25 Mar 2013
Posts: 88
Location: India

PostPosted: Thu Feb 02, 2017 3:08 pm
Reply with quote

Hello,

himanshu malik wrote:
...Required output should be

BBBBB 100

CCCCC 223 should not be in output file since 223 is not LT 222. ...
...


I beg to differ here. Fom your original post, the data you posted was:

himanshu malik wrote:

I have two files with same key which will be used in Join key, but I need a condition where a same field present in both files can be compared as less than, so that output will only have records from 2nd file where the same field is less than other.

Eg

File A File B
AAAAA 111 BBBBB 100
BBBBB 222 CCCCC 223
CCCCC 333 DDDDD 300

Output
BBBBB 100

Cond:1 Key will match, that can be handled by join key.
Cond 2 : 2nd field in File B < 2nd field in File A. How can this be handled.


As can be seen, 'CCCCC 333' is the data from 'file' A. Here the first 5 bytes i.e. 5 'C' are the key. Unless you're comparing data bits of records other than the KEY itself, '223' of 'C' from 'file' B will always be less than '333' of 'C' present in 'file' A.
If you feel otherwise, then kindly share correct representational data with conditions and we can guide you on the same.

Lastly, for quick details please refer the comments I'd added in the code shared, it briefly explains the code. To go in further detail, pick the keywords and look for them in the DFSORT Application Programming Guide, it explains them in detail.
Your code fails because you're using incorrect syntax for your JOINKEYS statement. It is coded as:

Code:

JOINKEYS FILE=F1,FIELDS=(1,5,ZD,A)
JOINKEYS FILE=F2,FIELDS=(1,5,ZD,A)


- which is basically in the form:

Code:

JOINKEYS FILE=F1,FIELDS=(<key-start-position>,<key-length>,<format of the key>,<order to sort the key>)


The correct syntax being:

Code:

JOINKEYS FILE=F1,FIELDS=(<key-start-position>,<key-length>,<order to sort the key>)


If you're new to DFSORT, a good place to start is: 'DFSORT Getting Started' manual for your site's z/OS release. It explains all these things in detail.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Feb 02, 2017 3:52 pm
Reply with quote

Himanshu Malik

Please use the code tags when presenting code, data and screen shots.
Please post in the correct part of the forum - you have a sort query not a JCL query.
Saying "it did not work" is useless unless you give the details, including full error messages, of the way it did not work.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top