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
 

 

Bringing two fields from different files using SORT step

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
santhoshm

New User


Joined: 13 Jun 2007
Posts: 32
Location: chennai

PostPosted: Wed Sep 26, 2007 11:43 am    Post subject: Bringing two fields from different files using SORT step
Reply with quote

Hi,
There are two files FILE1 and FILE2 with equal number of records sorted by two fields VAR1 and VAR2.
The files have 3 fields VAR1, VAR2, VAR3. The values of VAR2 in both the records may be different.

VAR1 of two files have to be compared if it match, then that record should be written to a new file FILE3 from FILE1 but the FILE3 should have records with VAR1, VAR2 from FILE1 and VAR3 from FILE2.

Is it possible to write like as above?
If YES please tell me.
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed Sep 26, 2007 11:53 am    Post subject:
Reply with quote

Santosh,
Quote:

VAR1 of two files have to be compared if it match, then that record should be written to a new file FILE3 from FILE1 but the FILE3 should have records with VAR1, VAR2 from FILE1 and VAR3 from FILE2.

Yes its possible. Usint ICETOOL's SPLICE option. For examples, serch DFSORT forum.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 26, 2007 6:26 pm    Post subject:
Reply with quote

Hello,

If you have problems, it will help if you post samples of the inputs and what you want the output to be.

You should also include recfm and lrecl.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 27, 2007 8:23 am    Post subject:
Reply with quote

Hello,

Do not post your question multiple times in multiple topics.

The newest one has been deleted.
Back to top
View user's profile Send private message
santhoshm

New User


Joined: 13 Jun 2007
Posts: 32
Location: chennai

PostPosted: Thu Sep 27, 2007 1:59 pm    Post subject:
Reply with quote

FILE1:

    --1----+----2----+----3----+----4----+----5----+----6--
    NS 34A Î.?8175MISX012716 12421 C0011234
    NS 34A a..8181NYSU041065 13301 C0312345


FILE2:

[list]
--1----+----2----+----3----+----4----+----5----+----6--
NS 34A Î.?8175MISX012726 12421 C0011234
NS 34A a..8181NYSU041065 13301 C0213232

I need out put like

--1----+----2----+----3----+----4----+----5----+----6--
NS 34A a..8181NYSU041065 13301 C0313232

i.e field from 34 to 51 are compared in FILE1 and FILE2 and if they match,
the FILE3 should have records from FILE1 but field from 56 to 113 has to get from FILE2.


Record length = 233
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Thu Sep 27, 2007 2:43 pm    Post subject: Reply to: Bringing two fields from different files using SOR
Reply with quote

Try this using Syncsort

Code:
//STEP03   EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD DSN=TEST.FILE1,DISP=SHR       
//SORTJNF2 DD DSN=TEST.FILE2,DISP=SHR
//SORTOUT  DD DSN=TEST.FILE3,DISP=OLD
//TOOLSMSG DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  JOINKEYS FILES=F1,FIELDS=(1,10,A)
  JOINKEYS FILES=F2,FIELDS=(1,10,A)
  REFORMAT FIELDS=(F1:1,28)                                     
  SORT FIELDS=(1,10,CH,A)                                       
  SUM FIELDS=NONE                                               
/* 
Back to top
View user's profile Send private message
santhoshm

New User


Joined: 13 Jun 2007
Posts: 32
Location: chennai

PostPosted: Thu Sep 27, 2007 7:07 pm    Post subject:
Reply with quote

Hi ,

Can you please help me. I've explained the problem below as well as the expected result.

I have two inputs.

Details of first input

Key field starts from position 7 to 18. File is sorted on the key.

Code:

 ***************************** Top of Data ******************************
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
0801012003000022  YXB001846A            0   5000000      8186.50       
0801012002000065  YXB002256             0   5000000      1763.00       
0801012000000128  YXB001625A            0  25000000     28051.42       
0801012000000048  YXB001860             0  20000000       126.50       
08010198000051    YXB001700             0  25000000     14200.15       
0801012002000073  YXB001237C            0  10000000      2628.05       
08010198000051    YXB001700             0  25000000       361.53       
08010198000093    YXB001751             0  10000000      1777.95       
0801012002000073  YXB001237C            0  10000000        60.50       
0801012002000048  YXB002358             0  10000000     25102.77       
**************************** Bottom of Data ****************************



Second input is also sorted on the same field as file 1. (Key positions 7 to 18)

Code:

sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
     GRAMASA.INPUT2                                  Columns 00001 00072
d ===>                                                  Scroll ===> CSR 
 ***************************** Top of Data ******************************
 ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 0801012003000022  YXB100000A      3453.00   2500000      7567.50       
 0801012002000065  YXB200000     242565.98   2423400     32343.00       
 0801012000000128  YXB300000A      3543.00  25675670    688851.42       
 0801012000000048  YXB348364             0  22342340    123326.50       
 08010198000051    YXB987750   23232232.00  25564570     18200.15       
 0801012002000049  YXB333333A    123424.00   5000000     25102.77       
 0801026406761     YYB300640A         5.98         0       752.92       
 08505007192001    YUB001489        543.00   5000000      4539.50       
 085050100501      YUB001664       6543.00  10000000      1350.00       
 08505082201       YUB001386E    194265.98   4000000     -4389.80       
 08505082201       YUB001383     294265.98   8000000     -8989.80       
 08505003G012      YUB300209D      2343.00    750000       935.00       
 08505098G028      YUB300209A       100.98    750000       852.50       
 0870602000030407  YXB300629A      2000.00   1000000      2588.73       
 **************************** Bottom of Data ****************************

Now the condition is

If the key field matches bet File 1 and 2,

then the record should be copied from file1 to file 3 (output file) and replace a few fields with values from file2. The fields that need to copied from File 1 is from Position 1 to 35 and again from 60 to end of the file.

From file 2, copy fields from position 36 to 59.

i.e. For the data that I've given above, only 5 records should be present on the o/p file. sample o/p for first matching record is given below
Code:

***************************** Top of Data ******************************
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
0801012003000022  YXB001846A      3453.00   2500000      8186.50       
0801012002000065  YXB002256     242565.98   2423400      1763.00       
0801012000000128  YXB001625A      3543.00  25675670     28051.42       
0801012000000048  YXB001860             0  22342340       126.50       
08010198000051    YXB001700   23232232.00  25564570     14200.15       


Please note that only if the key is present on both the files, it should appear on the o/p.

Please let me know if this is unclear.

Thanks
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 27, 2007 9:10 pm    Post subject:
Reply with quote

Hello,

Is this all part of the same "interview question"?

These posts seem to have little to do with other icon_confused.gif

Are you actually doing these on a mainframe somewhere?
Back to top
View user's profile Send private message
santhoshm

New User


Joined: 13 Jun 2007
Posts: 32
Location: chennai

PostPosted: Fri Sep 28, 2007 8:17 am    Post subject:
Reply with quote

Yes we are working on mainframes.
We need code for this. We have the following code, it is working when file 2 has no duplicate key field, when duplicate key field exists then record which is not present in file 1 is coming in file 3 (output file)

Code:
//FILE1 DD DSN=FILE1.INPUT,DISP=SHR                             
//FILE2 DD DSN=FILE2.INPUT,DISP=SHR                             
//T1    DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2    DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)   
//OUT   DD  DSN=FILE3.OUTPUT,                                   
//         DISP=(NEW,CATLG,DELETE),                             
//         UNIT=DISK,SPACE=(CYL,(5,1),RLSE),                   
//         DCB=(LRECL=233,RECFM=FB,DSORG=PS)                   
//TOOLIN DD *                                                   
 COPY FROM(FILE2) TO(T1) USING(CTL1)                           
 COPY FROM(FILE1) TO(T1) USING(CTL2)                           
 SPLICE FROM(T1) TO(T2) ON(34,18,CH) -                         
   WITHALL WITH(56,58) WITH(56,58)                             
 SORT FROM(T2) TO(OUT) USING(CTL3)                             
//CTL1CNTL DD *                                                 
 INREC BUILD=(1:1,33,34:34,18,52:52,182,234:8X)                 
/*                                                             
//CTL2CNTL DD *                                                 
 INREC OVERLAY=(234:SEQNUM,8,ZD)                               
//CTL3CNTL DD *                                                 
  SORT FIELDS=(234,8,ZD,A)                                     
  OUTREC BUILD=(1,233)                                         


Please let me if ur not clear.

thanks
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to change 'K' or 'M' use Sort vice_versa DFSORT/ICETOOL 5 Thu May 18, 2017 7:11 am
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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