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

Merge 2 input files after sort


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Wed Feb 15, 2023 8:09 pm
Reply with quote

Hi,

I have 2 files. For the specific condition match in 2nd file, I need to skip the relevant primary key details from file 1 as well in the output file but all records besides skipped records from file 1 must be present in output file.

I have tried Include and Omit but they are not working properly.

In file 2, we need to match 2 conditions and based on that records must be skipped from file 1.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1205
Location: Bamberg, Germany

PostPosted: Wed Feb 15, 2023 9:10 pm
Reply with quote

You have to show attempts to achieve the desired results, and also error messages you might have got. Also we would like to see input and desired output. Do not forget to use code tags (see button above the reply box) when presenting any code/data here.
Back to top
View user's profile Send private message
Garry Carroll

Senior Member


Joined: 08 May 2006
Posts: 1191
Location: Dublin, Ireland

PostPosted: Wed Feb 15, 2023 9:12 pm
Reply with quote

Quote:
I have tried Include and Omit but they are not working properly.


They are working properly, just not giving you the desired result.

Have you tried using JOINKEYS ? What are the conditions for omitting records from dataset 1 ? Can you give a sample of inputs and expected output (using code tags)?

Garry.
Back to top
View user's profile Send private message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Wed Feb 15, 2023 10:30 pm
Reply with quote

File 1 input -
ACC0001RNB
ACC0002RNB
ACC0003RNB
ACC0004RNB
ACC0005RNB
ACC0006RNB

File 2 input -
ACC0001C01
ACC000203L
ACC0002C03
ACC0003139
ACC0003A11
ACC0004C04


Here, for each account number, for position no 8, if we have 'C0' and '3' or '4' at 10th position, we don't need the relevant record from file 1 but all records beside those from File 1.

Expected output -
ACC0001RNB
ACC0003RNB
ACC0005RNB
ACC0006RNB
Back to top
View user's profile Send private message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Wed Feb 15, 2023 10:35 pm
Reply with quote

Below is the logic i have used -
I even tried the Include instead of Omit but its not giving the desired result.

SORT FIELDS=(1,7,CH,A)
JOINKEYS FILE=F1,FIELDS=(1,7,A)
JOINKEYS FILE=F2,FIELDS=(1,7,A)
OMIT COND=(8,2,CH,EQ,C'C0',AND,
(12,1,CH,EQ,C'3',OR,12,1,CH,EQ,C'4'))
REFORMAT FIELDS=(F1:1,489),FILL=C' '
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Wed Feb 15, 2023 10:36 pm
Reply with quote

Don't use pictures as attachment.

Use the Code button, and text copy of your screens. For your code, for your data, and for your output.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1205
Location: Bamberg, Germany

PostPosted: Wed Feb 15, 2023 11:09 pm
Reply with quote

Repeat from the beginning. Post (using code tags) what your input and desired output is. If you expect help, provide proper input. That is simple. All of us are volunteers.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Thu Feb 16, 2023 12:31 am
Reply with quote

Please, delete stupid attachment from the first post.
It is impossible to read the whole topic, because of this!
Back to top
View user's profile Send private message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Thu Feb 16, 2023 7:56 am
Reply with quote

sergeyken wrote:
Please, delete stupid attachment from the first post.
It is impossible to read the whole topic, because of this!


I am not able to delete the attachment.
I am attaching the scenario here again for reference.
-------------------------------------------------------------------------------------------------------------------------------------------------
I have 2 files. For the specific condition match in 2nd file, I need to skip the relevant primary key details from file 1 as well in the output file but all records besides skipped records from file 1 must be present in output file.

I have tried Include and Omit but they are not working properly.

In file 2, we need to match 2 conditions and based on that records must be skipped from file 1.

Below is the logic i have used -
I even tried the Include instead of Omit but its not giving the desired result.

SORT FIELDS=(1,7,CH,A)
JOINKEYS FILE=F1,FIELDS=(1,7,A)
JOINKEYS FILE=F2,FIELDS=(1,7,A)
OMIT COND=(8,2,CH,EQ,C'C0',AND,
(9,1,CH,EQ,C'3',OR,9,1,CH,EQ,C'4'))
REFORMAT FIELDS=(F1:1,489),FILL=C'
-------------------------------------------------------------------------------------------------------------------------------------------------
File 1 input -
ACC0001RNB
ACC0002RNB
ACC0003RNB
ACC0004RNB
ACC0005RNB
ACC0006RNB
-------------------------------------------------------------------------------------------------------------------------------------------------
File 2 input -
ACC0001C01
ACC000203L
ACC0002C03
ACC0003139
ACC0003A11
ACC0004C04


Here, for each account number, for position no 8, if we have 'C0' and '3' or '4' at 10th position, we don't need the relevant record from file 1 but all records beside those from File 1.

Expected output -
ACC0001RNB
ACC0003RNB
ACC0005RNB
ACC0006RNB
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1205
Location: Bamberg, Germany

PostPosted: Thu Feb 16, 2023 9:56 am
Reply with quote

Code:
//WHATEVER EXEC PGM=ICEMAN                             
//F1       DD *                                         
ACC0001RNB                                             
ACC0002RNB                                             
ACC0003RNB                                             
ACC0004RNB                                             
ACC0005RNB                                             
ACC0006RNB                                             
/*                                                     
//F2       DD *                                         
ACC0001C01                                             
ACC000203L                                             
ACC0002C03                                             
ACC0003139                                             
ACC0003A11                                             
ACC0004C04                                             
/*                                                     
//SYSOUT   DD SYSOUT=*                                 
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=F1,FIELDS=(1,7,A)                         
  JOINKEYS F2=F2,FIELDS=(1,7,A)                         
  JOIN UNPAIRED,F1,ONLY                                 
  END                                                   
/*                                                     
//JNF1CNTL DD *                                         
  END                                                   
/*                                                     
//JNF2CNTL DD *                                         
  INCLUDE COND=(8,2,CH,EQ,C'C0',AND,10,1,SS,EQ,C'34')   
  END                                                   
/*

Output:
Code:
****** ***************
000001 ACC0001RNB     
000002 ACC0003RNB     
000003 ACC0005RNB     
000004 ACC0006RNB     
****** ***************
Back to top
View user's profile Send private message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Thu Feb 16, 2023 10:38 am
Reply with quote

Joerg.Findeisen wrote:
Code:
//WHATEVER EXEC PGM=ICEMAN                             
//F1       DD *                                         
ACC0001RNB                                             
ACC0002RNB                                             
ACC0003RNB                                             
ACC0004RNB                                             
ACC0005RNB                                             
ACC0006RNB                                             
/*                                                     
//F2       DD *                                         
ACC0001C01                                             
ACC000203L                                             
ACC0002C03                                             
ACC0003139                                             
ACC0003A11                                             
ACC0004C04                                             
/*                                                     
//SYSOUT   DD SYSOUT=*                                 
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=F1,FIELDS=(1,7,A)                         
  JOINKEYS F2=F2,FIELDS=(1,7,A)                         
  JOIN UNPAIRED,F1,ONLY                                 
  END                                                   
/*                                                     
//JNF1CNTL DD *                                         
  END                                                   
/*                                                     
//JNF2CNTL DD *                                         
  INCLUDE COND=(8,2,CH,EQ,C'C0',AND,10,1,SS,EQ,C'34')   
  END                                                   
/*

Output:
Code:
****** ***************
000001 ACC0001RNB     
000002 ACC0003RNB     
000003 ACC0005RNB     
000004 ACC0006RNB     
****** ***************


I did not get where to include this below statement,
INCLUDE COND=(8,2,CH,EQ,C'C0',AND,10,1,SS,EQ,C'34') ?

I tried it this way but the output file is then empty,
//SYSIN DD *
OPTION COPY
JOINKEYS F1=F1,FIELDS=(1,7,A)
JOINKEYS F2=F2,FIELDS=(1,7,A)
INCLUDE COND=(8,2,CH,EQ,C'C0',AND,10,1,SS,EQ,C'34')
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,459),FILL=C''

END
/*
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1205
Location: Bamberg, Germany

PostPosted: Thu Feb 16, 2023 10:44 am
Reply with quote

Use the code I have provided.
Back to top
View user's profile Send private message
themants

New User


Joined: 11 Aug 2022
Posts: 8
Location: india

PostPosted: Thu Feb 16, 2023 12:38 pm
Reply with quote

Joerg.Findeisen wrote:
Use the code I have provided.


My organisation uses syncsort, can ICEMAN be used without any problem ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Feb 16, 2023 12:43 pm
Reply with quote

It should work what Joerg suggested. Try to run it .

Deleted the attachment and moved to SYNCSORT SECTION.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1992
Location: USA

PostPosted: Thu Feb 16, 2023 5:40 pm
Reply with quote

themants wrote:
Joerg.Findeisen wrote:
Use the code I have provided.


My organisation uses syncsort, can ICEMAN be used without any problem ?


SYNCTOOL for SYNCSORT provides (almost) the same functions as ICETOOL for DFSORT.
The difference is in two changed DDNAMEs. Please, RTFM by yourself.

SYNCSORT in your case should work in the same way as ICEMAN.
Why not just to try???!!!
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 -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 6
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top