Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Sort two file having same field, one fields Less than other.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Sort two file having same field, one fields Less than other.
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: 86
Location: India

PostPosted: Thu Feb 02, 2017 11:18 am    Post subject: Re: Sort two file having same field, one fields Less than other.
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    Post subject:
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    Post subject:
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: 86
Location: India

PostPosted: Thu Feb 02, 2017 1:17 pm    Post subject:
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    Post subject:
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: 86
Location: India

PostPosted: Thu Feb 02, 2017 3:08 pm    Post subject:
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: 1922
Location: UK

PostPosted: Thu Feb 02, 2017 3:52 pm    Post subject:
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    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
No new posts VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm
This topic is locked: you cannot edit posts or make replies. Need help how to pass the ESP variabl... Venkichaitu JCL & VSAM 12 Thu Nov 02, 2017 3:53 am
This topic is locked: you cannot edit posts or make replies. Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us