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

Bringing two fields from different files using SORT step
IBM Mainframe Forums -> Mainframe Interview Questions
Post new topic   Reply to topic Quick References
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
 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: 1436
Location: Bangalore,India
PostPosted: Wed Sep 26, 2007 11:53 am
 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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Wed Sep 26, 2007 6:26 pm
 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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Thu Sep 27, 2007 8:23 am
 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
 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
 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
 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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Thu Sep 27, 2007 9:10 pm
 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
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> Mainframe Interview Questions

 

Similar Topics
Topic Forum Replies
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
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 JCL sort card - get first day and las... JCL & VSAM 9
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us