Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Combine matching records from two different input, and forma

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Fri Apr 20, 2007 2:13 am    Post subject: Combine matching records from two different input, and forma
Reply with quote

Frank,

This is almost similar to the question you have already answered, but with minimal requirement changes.


Input1:
Column : Col1 Col2 Col3 Col4 Col5
Position : 1-5 6-10 11-15 16-26 27-40

Input2:
Column : Col1 Col2 Col6 Col7 Col8
Position : 1-5 6-10 11-15 16-26 27-40


For all matching Col1, Col2 in both the inputs
Output: Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8

If Input1 has the key but not Input2 then it should be reported to another file as
Col1 Col2 Col3 Col4 Col5 blank blank blank


Here is the example:

iNP1:
AAA 1111 A1A1A1 A2A2A2 A3A3A3
AAA 2222 A1A1A1 A2A2A2 A3A3A3
BBB 1111 B1B1B1 B1B1B1 B1B1B1

INP2:

AAA 1111 X1X1X1 X2X2X2 X3X3X3
AAA 2111 AXAXAX AXAXAX AXAXAX
BBB 1111 BXBXBX BXBXBX BXBXBX

OUTPUT:

AAA 1111 A1A1A1 A2A2A2 A3A3A3 X1X1X1 X2X2X2 X3X3X3
AAA 2222 A1A1A1 A2A2A2 A3A3A3 N/A N/A N/A
BBB 1111 B1B1B1 B1B1B1 B1B1B1 BXBXBX BXBXBX BXBXBX

INSTEAD OF N/A you can write any character say 'XXXX' etc.
Back to top
View user's profile Send private message

jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Fri Apr 20, 2007 2:15 am    Post subject: Re: Combine matching records from two different input, and f
Reply with quote

My mistake, instead of another file, it should be written to same file..
The example is correct.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 20, 2007 4:27 am    Post subject:
Reply with quote

What do you want to do with a record in input2 that doesn't have a match in input1?

Can there be duplicates in input1? If so, show an example.

Can there be duplicates in input2? If so, show an example.
Back to top
View user's profile Send private message
jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Fri Apr 20, 2007 7:50 pm    Post subject:
Reply with quote

Frank,

Col1 + col2 is the key, this combination is unique.

There wont be any duplicate keys in any of the input

Input1 is the file of our concern,
For every record of Input1 check for a match in input2.
(If Input2 has a record, and Input1 doesn't then ignore it)


iNP1:
Code:

Col1 Col2
AAA  1111 A1A1A1 A2A2A2 A3A3A3
AAA  2222 A1A1A1 A2A2A2 A3A3A3
BBB  1111 B1B1B1 B1B1B1 B1B1B1


INP2:
Code:

Col1  Col2
AAA  1111 X1X1X1 X2X2X2 X3X3X3
AAA  2111 AXAXAX AXAXAX AXAXAX
BBB  1111 BXBXBX BXBXBX BXBXBX


OUTPUT:

Code:

AAA  1111 A1A1A1 A2A2A2 A3A3A3 X1X1X1 X2X2X2 X3X3X3
AAA  2222 A1A1A1 A2A2A2 A3A3A3 N/A    N/A    N/A
BBB  1111 B1B1B1 B1B1B1 B1B1B1 BXBXBX BXBXBX BXBXBX
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 20, 2007 10:07 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for. I'm not sure if I have the column positions right but this should give you the idea and you can adjust the positions as needed.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/30)
//IN2 DD DSN=...  input file2 (FB/30)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/51)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,9,CH) KEEPNODUPS -
  WITH(32,20) WITH(52,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(33:C'N/A',40:C'N/A',47:C'N/A',52:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(1,9,32:11,20,52:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,OMIT=(52,2,CH,EQ,C'VV'),
    BUILD=(1,51)
/*
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us