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.
...
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.
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
/*
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:
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.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
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.