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

Join records from 2 files with No Duplicates using DFSORT


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

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Sun Aug 27, 2017 10:35 pm
Reply with quote

Hi!
This is my first post in the forum. I am working on a SORT card in which i am reading records from 2 input files and joining them on a 15 byte key. But the issue is, in both the files few records have duplicate key. So due to these duplicate keys it is creating cartesian product of records in output file. My requirement is to write one to one matching record. I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement. But even after using this option in my SORT i am not getting the expected output. I can really use some help here as i need to deliver this tomorrow. Please let me know what i am missing here. Many Thanks !
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Mon Aug 28, 2017 1:37 am
Reply with quote

do not post images, a plain TEXT cut and paste from Your emulator window is more than enough.

the images will be deleted shortly
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Aug 28, 2017 3:35 am
Reply with quote

Quote:

I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement


SORTED,NOSEQCK indicates records are already in order by the specified binary key, Sequence check will not be performed by dfsort.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Aug 28, 2017 3:54 am
Reply with quote

Assuming both the files are in sorted order.

change the joinkeys as follows

UNTESTED
Code:

JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK


Try Adding following statements.
Code:

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))         


Let us know the results.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Aug 28, 2017 4:10 am
Reply with quote

NOTE

In case you have more than one record for a key in file1 and only one matching key/record in file2, then only one record would be extracted from the input, the other records would be skipped.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Aug 28, 2017 3:04 pm
Reply with quote

Hi Enrico,

I will keep this in mind going forward. Thanks !

Hi Magesh,

The sort card you provided has worked and i am getting the exact results what i was expecting. I will post the results shortly. Thank you very much for this icon_biggrin.gif

I want to understand the meaning of what you coded :
JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK

Que - In above card for File F1 and File F2, is it sorting on 4 bytes from 174 and 068 ? I am confused about this part. Please guide here.

//JNF1CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))

Que : what does it means WHEN=GROUP in above card and does the PUSH keyword inserting a sequence no. of 4 bytes here ? If yes, then i will be having more than 6 lakhs records in my files so is it correct if i need to increase the length of SEQ from 4 to 6 ?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


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

PostPosted: Mon Aug 28, 2017 3:23 pm
Reply with quote

"lakhs" is not understood - please use English terminology.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Aug 28, 2017 4:59 pm
Reply with quote

Hi Nic,

I will be having 600,000 records in my file.


Thanks !
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Mon Aug 28, 2017 7:45 pm
Reply with quote

magesh23586 wrote:
Assuming both the files are in sorted order.

change the joinkeys as follows

UNTESTED
Code:

JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK


Try Adding following statements.
Code:

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))         

Let us know the results.

It is not obvious that this approach will work, because the topic starter did not provide enough information on his requirements.
What is the role of each of joined files in his example?
For instance, both datasets have two records each with the same key field
Code:
2307914558..023
(assuming non-printed bytes '..' are the same? Or not?)
In terms of defined JOIN operation that means that the output file should include (2 * 2) = 4 output records with the same key; every record of F1 is matching with every record of F2. The given example demonstrates exactly the result which is expected by JOIN definition.

In the suggested solution with sequentially numbered records, for the given example the first matching F1 record will match with the first of F2 record, etc., but not clear if this is what the TS wanted from the beginning?
Let's say there are 100 same key records in F1, and 2 same key records in F2; in that case the SEQ= solution will produce 2 of the first matching records, and the rest 98 records of F1 should be silently ignored.
When using the original TS solution, this case should produce (100 * 2) = 200 output records.

Again: from the TS description it is not clear what is the actual task, and/or what is the role of each F1/F2 in his job?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Mon Aug 28, 2017 8:39 pm
Reply with quote

Poha Eater wrote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

I would recommend, before you get some basic knowledge about SORT/JOIN/MERGE processing, and used methods, - not to use the NOSEQCK option. Please, postpone even use of the SORTED option - at least until you're able to create yourself (not via copy-paste!) at least primitive, but really working, jobs for DFSORT/SYNCSORT.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Aug 28, 2017 8:45 pm
Reply with quote

Poha Eater wrote:

Que - In above card for File F1 and File F2, is it sorting on 4 bytes from 174 and 068 ? I am confused about this part. Please guide here.


Poha Eater wrote:

what does it means WHEN=GROUP in above card


Refer DFSORT Application programming guide.

Quote:

does the PUSH keyword inserting a sequence no. of 4 bytes here ? If yes, then i will be having more than 6 lakhs records in my files so is it correct if i need to increase the length of SEQ from 4 to 6 ?


No, Not required, unless if you have more than 10K records having same key value.

I belive even 2 bytes is sufficient, i dont think you will have more than 99 records having same key.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Aug 28, 2017 9:04 pm
Reply with quote

Welcome!!
Quote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

You havn't done a proper research.

Why don't you eliminate duplicate before JOINKEYS by adding this piece to what you have posted?
Code:
//JNF1CNTL DD *     
  SUM FIELDS=NONE   
//*

Code:
//JNF2CNTL DD *     
  SUM FIELDS=NONE   
//*

or also try solution provide here or given above by magesh23586,
www.ibmmainframeforum.com/dfsort-icetool-icegener/topic5914.html
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Mon Aug 28, 2017 9:26 pm
Reply with quote

Rohit Umarjikar wrote:
Welcome!!
Quote:
I am using DFSORT and for this i have searched in the forum that SORTED,NOSEQCK should be used to accomplish my requirement.

You havn't done a proper research.

Why don't you eliminate duplicate before JOINKEYS by adding this piece to what you have posted?
Code:
//JNF1CNTL DD *     
  SUM FIELDS=NONE   
//*

Code:
//JNF2CNTL DD *     
  SUM FIELDS=NONE   
//*

or also try solution provide here or given above by magesh23586,
www.ibmmainframeforum.com/dfsort-icetool-icegener/topic5914.html

A have to repeat again: it is not clear what is the role of each F1/F2 in the original task? Why multiple duplicate keys can appear in both F1+F2? What to do with duplicated key in each of them?

Eliminating duplicate keys via SUM FIELDS=NONE will create a single matching record produced, but (in general case) created from an unpredicted pair of matching keys.

Matching the pairs of records via use of re-numbered (via SEQ=) groups of records will create matching pairs from sequential duplicate key records, up to the minimum in each of F1, or F2; while the rest of same key records will be ignored.

The TS must clearly explain what his real requirements are, before trying to use (unknown to him) options of SORT control statements. Before clear understanding of the task, no advice from the Forum, and no control statement option would help. Period.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Aug 29, 2017 10:12 pm
Reply with quote

Quote:
Eliminating duplicate keys via SUM FIELDS=NONE will create a single matching record produced, but (in general case) created from an unpredicted pair of matching keys.

He could use SUM FIELDS on one ds to get what he wants.
Poha Eater, please respond to to clear the air of doubts in the requirements to get it further moving else it can be locked.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Aug 30, 2017 12:00 am
Reply with quote

Hi All,

Thanks for all your responses. It was really helpful and informative. Let me reiterate my requirement and before that i accept that i didnt mention in my first post that in 2nd file when the key is same for 2 records but in these 2 records data is not same after the key. I did attached the screenshot of my File 2 which is showing different data for each row when there is duplicate key.

My requirement is - I have 2 files. Each files having a key of first 15 byte (starting from column 1 to 15). First file is having record length of 173 and second file has the record length of 251.

Both the files have exact same number of records. Suppose if file 1 has 1000 records then file 2 will also have 1000 records. In both the files, data only on first 15 bytes will be same, which is also key to my JOIN condition here.

In the output file, i have write all the 173 bytes of data from the File 1 and from File 2 i have to write the data of length 39 and starting from 28th byte.

In the File 1, when there is any record which is having duplicate key, the rest of the data in each record is also same whereas in File 2 whenever there is any duplicate key, the data in each record after the key (after the 15th byte) is not same and as per my requirement both files have exact same number of records so i have to join each row of both files and that is why i could not use the SUM FIELDS=NONE here because in File 2 it will remove the second record with same key but the second record have different data after the first 15 bytes from the first record with same key. I have attached the pics of File 2 data earlier. Pasting the data of File 2 below as well to show that it has different data in each row because i dont know how paste the image of file here. I tried using Img button, nothing is happening, i am sorry.

2307914558,,,,,,,,,,,,MD,1,2013,-62,,FVT,21117,LNL,20100
2307914558,,,,,,,,,,,,MD,1,2013,-93,,FVT,21117,LNL,8500G

Thank you magesh23586 for providing the resolution. The results are coming exactly as per the attached image from my first post, named as expected output.

Thanks all for your responses !!
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Sep 06, 2017 2:03 am
Reply with quote

magesh23586 wrote:
NOTE

In case you have more than one record for a key in file1 and only one matching key/record in file2, then only one record would be extracted from the input, the other records would be skipped.


Hi Magesh,
Due to enhancement in my requirement, now i have more than 1 record for a key in File 1 but only 1 matching record in File 2. So is it possible by any means to still join the one to one record based on same key and also get all the records form file 1 for the same key in output file ?

Now i don't have equal number of records in File 1 and File 2.

Example :

File 1 File2
********************************* *************************
0000L01455 ,xxx,yyy,ABCDEF, 0000L01455 ,2013,-2200.22,
4814375 ,xxx,yyy,GHIJKLMNO, 4814375 ,2013,-5122,
4814375 , , ,PQRSTUV, 0000L01762 ,2013,-100,
0000L01762 ,xxx,yyy,WXYZAB, 0000L02594 ,2013,-1056,
0000L02594 ,xxx,yyy,CDEFGHIJKL, 4828156 ,2013,-3369,
4828156 ,xxx,yyy,MNOPQRST, 0000L02908 ,2013,-2440,
4828156 , , ,UVWXY, 2307914558 ,2013,-62,
0000L02908 ,xxx,yyy,ZABCDEFGH, 2307914558 ,2013,-93,
2307914558 ,xxx,yyy,IOSIF W,
2307914558 ,xxx,yyy,IOSIF W,


Output should look like below : (Key is first 15 byte in both the files)

**********************************************************
0000L01455,xxx,yyy,ABCDEF,2013,-2200.22,
4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
4814375, , ,PQRSTUV,
0000L01762,xxx,yyy,WXYZAB,2013,-100,
0000L02594,xxx,yyy,CDEFGHIJKL,2013,-1056,
4828156,xxx,yyy,MNOPQRST,2013,-3369,
4828156, , ,UVWXY,
0000L02908,xxx,yyy,ZABCDEFGH,2013,-2440,
2307914558,xxx,yyy,IOSIF W,2013,-62,
2307914558,xxx,yyy,IOSIF W,2013,-93,
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Sep 06, 2017 2:24 am
Reply with quote

Poha Eater wrote:
Due to enhancement in my requirement, now i have more than 1 record for a key in File 1 but only 1 matching record in File 2. So is it possible by any means to still join the one to one record based on same key and also get all the records form file 1 for the same key in output file ?

Now i don't have equal number of records in File 1 and File 2.

Example :

Code:
File 1                                  File2
*********************************    *************************   
0000L01455    ,xxx,yyy,ABCDEF,         0000L01455     ,2013,-2200.22,
   4814375       ,xxx,yyy,GHIJKLMNO,      4814375        ,2013,-5122,
   4814375       ,   ,    ,PQRSTUV,    0000L01762     ,2013,-100,
0000L01762    ,xxx,yyy,WXYZAB,         0000L02594    ,2013,-1056,
0000L02594    ,xxx,yyy,CDEFGHIJKL,        4828156        ,2013,-3369,
   4828156       ,xxx,yyy,MNOPQRST,    0000L02908    ,2013,-2440,
   4828156       ,   ,    ,UVWXY,      2307914558    ,2013,-62,
0000L02908    ,xxx,yyy,ZABCDEFGH,      2307914558    ,2013,-93,
2307914558    ,xxx,yyy,IOSIF W,
2307914558    ,xxx,yyy,IOSIF W,


Output should look like below : (Key is first 15 byte in both the files)

Code:
**********************************************************
     0000L01455,xxx,yyy,ABCDEF,2013,-2200.22,
        4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
        4814375,   ,    ,PQRSTUV,
     0000L01762,xxx,yyy,WXYZAB,2013,-100,
     0000L02594,xxx,yyy,CDEFGHIJKL,2013,-1056,
        4828156,xxx,yyy,MNOPQRST,2013,-3369,
        4828156,   ,    ,UVWXY,
     0000L02908,xxx,yyy,ZABCDEFGH,2013,-2440,
     2307914558,xxx,yyy,IOSIF W,2013,-62,
     2307914558,xxx,yyy,IOSIF W,2013,-93,



1. RTFM:

1.1 JOINKEYS statement
1.2 JOIN statement
1.3 REFORMAT statement

2. This must be enough to start your task

2.1 create your own code to perform the task
2.2 perform test run
2.3 verify the results, and/or error codes
2.4 fix obvious errors
2.5 present to the forum what is really not clear to yourself

Please, do not try to do the above said in opposite order!
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Sep 06, 2017 7:57 am
Reply with quote

Try adding Join statement after joinkeys.
Code:


JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK
JOIN UNPAIRED,F1


But how do you determine which record to join and which record not to join ?

Example

Your joined records
Code:
   
4814375,xxx,yyy,GHIJKLMNO,2013,-5122,
4814375,   ,    ,PQRSTUV,

why not like below ?
Code:

4814375,xxx,yyy,GHIJKLMNO,
4814375,   ,    ,PQRSTUV,2013,-5122,



There should be some relationship/logic behind it, you need to understand the requirement correctly else you may have incorrect output.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Sep 06, 2017 12:20 pm
Reply with quote

Hi Magesh,

Thanks for answering. The requirement is to Join the first record for any key from File1 to the first record of that key from File 2 (2nd File will have only 1 matching key/record) and 1st File can have more than 1 records per key, then those records should also be merged in the output file with the Joined records in the same serial as it was giving in 1st File.

I believe i also need to add the below code as well in SORT as i still have multiple records for 1 key in both files for which i need to do 1 to 1 matching (first record for a key from File 1 should be joined with the first record of File 2 and 2nd record for the same key should only join with the 2nd record of File 2 , there should be no cartesian product of records while joining (as i posted in my first requirement)).

Code:
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Wed Sep 06, 2017 6:43 pm
Reply with quote

Poha Eater wrote:
Hi Magesh,

Thanks for answering. The requirement is to Join the first record for any key from File1 to the first record of that key from File 2 (2nd File will have only 1 matching key/record) and 1st File can have more than 1 records per key, then those records should also be merged in the output file with the Joined records in the same serial as it was giving in 1st File.

I believe i also need to add the below code as well in SORT as i still have multiple records for 1 key in both files for which i need to do 1 to 1 matching (first record for a key from File 1 should be joined with the first record of File 2 and 2nd record for the same key should only join with the 2nd record of File 2 , there should be no cartesian product of records while joining (as i posted in my first requirement)).

Code:
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))


Still ur answer is not convincing..Good luck.

Yes, you need to add those statements.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Sep 06, 2017 8:58 pm
Reply with quote

With your requirements, the options SORTED,NOSEQCK will not work as you wish.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Sep 06, 2017 10:06 pm
Reply with quote

If you don't care about the final order of records, and/or alignment of your fields/commas, then this approach must work
Code:
//SORTJOIN EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//MASTER   DD  *                                               
*---+----1----+----2----+----3----+----4----+----5----+----6   
0000L01455    ,XXX,YYY,ABCDEF,                                 
4814375       ,XXX,YYY,GHIJKLMNO,                             
4814375       ,   ,   ,PQRSTUV,                               
0000L01762    ,XXX,YYY,WXYZAB,                                 
0000L02594    ,XXX,YYY,CDEFGHIJKL,                             
4828156       ,XXX,YYY,MNOPQRST,                               
4828156       ,   ,   ,UVWXY,                                 
0000L02908    ,XXX,YYY,ZABCDEFGH,                             
2307914558    ,XXX,YYY,IOSIF W,                               
2307914558    ,XXX,YYY,IOSIF W,                               
//DETAILS  DD  *                                               
*---+----1----+----2----+----3----+----4----+----5----+----6   
0000L01455    ,2013,-2200.22,                                 
4814375       ,2013,-5122,                                     
0000L01762    ,2013,-100,                                     
0000L02594    ,2013,-1056,                                     
4828156       ,2013,-3369,         
0000L02908    ,2013,-2440,         
2307914558    ,2013,-62,           
2307914558    ,2013,-93,           
//SORTOUT  DD SYSOUT=*             
//JNF1CNTL DD *                   
 OMIT COND=(1,1,CH,EQ,C'*')       
 INREC IFTHEN=(WHEN=GROUP,         
               KEYBEGIN=(1,15),   
               PUSH=(81:SEQ=4))   
//JNF2CNTL DD *                   
 OMIT COND=(1,1,CH,EQ,C'*')       
 INREC IFTHEN=(WHEN=GROUP,         
               KEYBEGIN=(1,15),   
               PUSH=(81:SEQ=4))   
//SYSIN    DD *                   
 JOINKEYS F1=MASTER,               
          FIELDS=(01,15,A,         
                  81,4,A)         
 JOINKEYS F2=DETAILS,             
          FIELDS=(01,15,A,                       
                  81,4,A)                       
 JOIN UNPAIRED,F1                               
 REFORMAT FIELDS=(F1:1,14,                       
                  F1:15,30,                     
                  F2:15,30),                     
         FILL=C' '                               
 SORT FIELDS=COPY                               
 OUTREC BUILD=(1,74,SQZ=(SHIFT=LEFT,MID=C' '))   
 END                                             
//*                                             

The result is like this
Code:
----+----1----+----2----+----3----+----4----+----5----+----6
0000L01455 ,XXX,YYY,ABCDEF, ,2013,-2200.22,                 
0000L01762 ,XXX,YYY,WXYZAB, ,2013,-100,                     
0000L02594 ,XXX,YYY,CDEFGHIJKL, ,2013,-1056,               
0000L02908 ,XXX,YYY,ZABCDEFGH, ,2013,-2440,                 
2307914558 ,XXX,YYY,IOSIF W, ,2013,-62,                     
2307914558 ,XXX,YYY,IOSIF W, ,2013,-93,                     
4814375 ,XXX,YYY,GHIJKLMNO, ,2013,-5122,                   
4814375 , , ,PQRSTUV,                                       
4828156 ,XXX,YYY,MNOPQRST, ,2013,-3369,                     
4828156 , , ,UVWXY,                                         
******************************** Bottom of Data ************

You can customize it as per additional rerquirements.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Oct 04, 2017 11:43 pm
Reply with quote

Hi Sergeyken,

Thanks a lot for your generous efforts in resolving my query. The below line in my output, it was just for representation purpose to show what values are coming at which column.

Code:

*---+----1----+----2----+----3----+----4----+----5----+----6


The control card which Magesh provided is working fine for my requirement. The only issue i am facing is that both the files are in unsorted order so when i am using the below code, it is only working till it is finding the key in sorted order. When it encounters the record whose key is not according to the sort, it is not joining the records.

I wanted to ask how can i modify the below sort card so that it can join the records when both the files are in unsorted order.

Code:
JOINKEYS FILES=F1,FIELDS=(01,15,A,174,4,A),SORTED,NOSEQCK   
JOINKEYS FILES=F2,FIELDS=(01,15,A,068,4,A),SORTED,NOSEQCK

//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(174:SEQ=4))         
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(068:SEQ=4))


Please note that due to the nature of my requirement i can not write the output in sorted order.

Many Thanks !!
icon_smile.gif
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 Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top