Matching FILE A and FILE B based on ACCOUNT NUMBER

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

Hi All,

I have a problem which requires your help.

There is file A with fields

1. ACCOUNT NUMBER
2. STATUS
3. DATE 1
4. DATE 2

Ther is file B with fields

1. ACCOUNT NUMBER
2. STATUS
3. DATE3

I need an output file by matching FILE A and FILE B based on ACCOUNT NUMBER and STATUS and create the output file with fields

ACCOUNT NUMBER
STATUS
DATE 1
DATE 2
DATE 3

Thanks in advance for your help

Warning: Title edited and dup. post removed

Frank Yaeger · Posted: Wed Dec 06, 2006 11:54 pm

What is the starting position, length and format of the ACCOUNT NUMBER, STATUS, DATE1, DATE2 and DATE3 fields? What is the RECFM and LRECL of each input file? Do you want to omit non-matching records from the output file?

Please show an example of the input records in each file and the records you want for output. If file A can have duplicates within it, show that in the example. If file B can have duplicates within it, show that in the example.

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

EXAMPLE:

FILE A

RECORD 1 => 123456789012345QW10.12.200620.12.2006
RECORD 2 => 123456789034567QE10.12.200620.12.2006
FILE B

RECORD 3 => 123456789012345QW12.12.2006

REQUIRED OUTPUT FILE

FIRST 18 character should match between FILE A and FILE B
that is 123456789012345QW
matching fields

123456789012345QW10.12200620.12.200612.12.2006

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

Please excuse...

Some more info FILE A and FILE B does not have duplicates.

Frank Yaeger · Posted: Thu Dec 07, 2006 2:00 am

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

HI Frank,

I use the following JCL. I think i did not try the correct way. Can you help please?

Frank Yaeger · Posted: Thu Dec 07, 2006 10:55 pm

You missed one very important thing.

In my job, I have:

//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)

This is a MOD data set.

In your job, you have:

//T1 DD DSN=EORGA.T1,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(50,50),RLSE)

This is not a MOD data set. T1 must be a MOD data set. Fix that and your job will run correctly.

jagankallis · New User Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG

Thanks a lot Frank, the solution is working fine,but same question again...
Can you please explain in a more detail way, how this works and What is SPLICE?

Frank Yaeger · Posted: Thu Dec 07, 2006 11:21 pm

For complete details on the SPLICE operator of DFSORT's ICETOOL (with examples), see:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links

Prabhu Kvvsrt · New User Joined: 06 Dec 2006 Posts: 7 Location: Hyderabad

In this regard : I have a question

what if we have duplicates in the first file itself and we need all the dups in it

Prabhu Kvvsrt · New User Joined: 06 Dec 2006 Posts: 7 Location: Hyderabad

Actually My Requirement is

1) File A with some fields of length 500
2) File B with some fields of lenght 600

Now in the both the files two fields exist at same position and
both the files have same number of records.

Suppose position of two fields is 50:10 then i have to compare them in both the files and get all the records from the file 1 with two more fields appended to it and those two fields are from file 2.

I tried your SPLICE logic but i am UNABLE to get all the dups from the file1
I mean i need the dups also from the file 1.

Thanks,
Prabhu

Frank Yaeger · Posted: Sun Dec 10, 2006 8:35 pm

Pabhu,

It's not clear what you want to do. Please show an example of the records (relevant fields) in each input file, and what you expect for the output records. Give the starting position, length and format of the relevant fields.

Prabhu Kvvsrt · New User Joined: 06 Dec 2006 Posts: 7 Location: Hyderabad

Hi Frank,

I found the solution for gettting the duplicates also from file1.

For your reference

File 1 :
Customer Number 9 Characters
Amount 6 Characters
Customer Name 6 Characters
Due Date 8 Characters

File 2:
Customer Number 9 Characters
Amount 6 Characters
Customer Address 10 Characters
Customer Pincode 6 Characters

if FILE 1 has duplicates as follows
000000001250000CLARKE20061211
000000001250000CLARKE20061213

File 2 :
000000001250000JameCircle600030
000000001250000JameCircle600030

In this case if we splice ON (CUSTOMERNUMBER,AMOUNT) WITH(CUSTOMER ADDRESS,CUSTOMER PINCODE) in the output file we get only one record from FILE1
I.E 1st record : 000000001250000CLARKE20061211 JameCircle600030
but not the 2nd record as it is a duplicate in FILE1.

You might say that you can change the ON Paramater but sometimes date might be the same in that case we get only one record from file1

My requirement was to get all the records from the FILE1.
File1 and File2 will have same number of records.

I resolved it by adding SEQNUM in OUTREC FIELDS of both the files and then SPLICING it ON(CUSTOMERNUMBER,AMOUNT,SEQNUM).

Please let me know if there is any other way to do it.

Thanks,
Prabhu

Frank Yaeger · Posted: Mon Dec 11, 2006 9:23 pm