Posted: Wed May 14, 2008 3:36 am Post subject: Problem by searching missing record and union
Hi Guys,
sorry to bother you, I've a weird problem and no idea on how to solve it, maybe you can help me.
I have two files like these:
File1
Code:
1 A data1
1 B data1
2 B data1
2 B data2
3 B data1
4 A data1
4 B data1
4 B data2
File2:
Code:
1 A data2
2 A data2
3 A data2
4 A data2
In the first file I have two kind of record: A and B, joined by the numer in first position.
For every group of B record (with the same numer in first position) in file1 I need to have one A record. If I do not have it in file1 I need to get it in file2.
So for example record 1 as A and B so is ok, record 2 (where I have two B record), neeed a A record and I need it from file2.
At the end my output should be:
Code:
1 A data1 <- original from from file1
1 B data1
2 A data2 <- from file2
2 B data1
2 B data2
2 A data2 <- from file2
3 B data1
4 A data1 <- original from from file1
4 B data1
4 B data2
do you have any idea on how can I do? I thougnt I could add a number at the end of record A, join it together with the file2 and sort this file, then rejoin it with the first file... but it is not clear in my mind
we can suppose that the files are FB with LREC=9. there is not an unique key, the first byte a number to concatenate anch in position 3 there is the type of record.
Anyway I found a solution and i want to expose to you.
With this first step i get the record type B that do not have a record type A:
Code:
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD *
1 B DATA1
2 B DATA1
2 B DATA2
1 A DATA1
3 B DATA1
4 A DATA1
4 B DATA1
4 B DATA2
//TEMP0 DD SYSOUT=*
//T0 DD DSN=&T1,SPACE=(CYL,(500,500),RLSE),DISP=(,PASS)
//T1 DD DSN=&T1,SPACE=(CYL,(500,500),RLSE),DISP=(,PASS)
//T2 DD DSN=&T2,SPACE=(CYL,(500,500),RLSE),DISP=(,PASS)
//TOOLIN DD *
SORT FROM(FILE1) TO(T0) USING(CTLA)
SORT FROM(T0) TO(T1) USING(CTLB)
SORT FROM(T1) TO(TEMP0) USING(CTLC)
//CTLACNTL DD *
SORT FIELDS=(1,3,CH,A)
//CTLBCNTL DD *
SORT FIELDS=(1,1,CH,A)
SUM FIELDS=NONE
//CTLCCNTL DD *
SORT FIELDS=(1,3,CH,A)
INCLUDE COND=(3,1,CH,EQ,C'B')
Now I will get the A record in the File2 for these record.
Do you think is a good idea or you see some other "road-map"?
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu May 15, 2008 10:17 am Post subject:
maxisnowhere,
Here is a simple one pass DFSORT solution which will give you the desired results.
Concatenate both files together to sortin and we sort on the first 3 bytes of the key. We use a seqnum with restart parm to see if the key already existed in file1 and then eliminate it if found.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
