|
View previous topic :: View next topic
|
| Author |
Message |
mvmadhavi
New User
Joined: 09 Nov 2006
Posts: 21
|
|
|
|
Hi,
My requirement is as below
i have two files
file 1
| Code: |
subscription status order no
000097636347KK6414656
000097636347KK6416152
000097641712KK6414657
000097641712KK6416153
000097673341 0000000
000097673462KK6415711
000097673462KK6417062 |
File 2
| Code: |
CON0000000 XXX00009763634700000000000
ASC0000000 XXX00009763634700000000000
CON0000000 XXX00009764171200000000000
ASC0000000 XXX00009764171200000000000
CON0000000 XXX00009767334100000000000
ASC0000000 XXX00009767334100000000000
|
While using join keys
I picking up only order no's from first file(example:6416152)
and placing it as below in file 2
| Code: |
CON6414656 XXX00009763634700000000000
ASC6416152 XXX00009763634700000000000
CON6414657 XXX00009764171200000000000
ASC6416153 XXX00009764171200000000000
CON0000000 XXX00009767334100000000000
[b]ASC6415711 XXX00009767334100000000000[/b]
CON0000000 XXX00009767346200000000000
|
The problem is for the number 000097673341 there is only one record in file 1 but while using join keys the order no 6415711 which is actually for 000097673462 will be placed for 000097673341.But actually the order 6415711 should have been placed for 000097673462.
is there any better method to do this so that if the numbers dont match then simply write the record. with out the order no then write the correct order no to correct subscription.
The join key used is
| Code: |
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(141,8,A),SORTED,NOSEQCK
JOINKEYS F2=IN2,FIELDS=(22,8,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:1,140,F2:1,21)
OPTION COPY
OUTREC FIELDS=(1:1,45,155,7,153,2,55,95)
/*
//*
//JNF1CNTL DD *
INREC OVERLAY=(141:SEQNUM,8,BI)
/*
//JNF2CNTL DD *
INREC OVERLAY=(22:SEQNUM,8,BI)
|
File 1 is 21 bytes and file 2 is 140 bytes |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
If you are using a sequence number for joining it will always create a one-for-one match between the two files based solely on record order. This technique if for when there is no key that can match between the two files, and such a physical relationship exists.
Since you seem to have the same key on both files, why don't you use the key? |
|
| Back to top |
|
|
mvmadhavi
New User
Joined: 09 Nov 2006
Posts: 21
|
|
|
|
But the key (subscription) could be duplicates as we see in the files
CON6414656 XXX00009763634700000000000
ASC6416152 XXX00009763634700000000000 |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
So you have to deal with it with some code.
If you have the duplicate keys, what matching do you want to take place?
Can there be duplicates on both files? |
|
| Back to top |
|
|
mvmadhavi
New User
Joined: 09 Nov 2006
Posts: 21
|
|
|
|
even though keys are duplicate matching has to be on subscripton.
there could be duplicates on both files |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
mvmadhavi,
It is not clear as to how the matching is supposed to be done.
| Code: |
000097636347 KK 6414656
000097636347 KK 6416152
|
You have 2 records for the subscription 000097636347 in both files.
| Code: |
CON0000000 XXX 000097636347 00000000000
ASC0000000 XXX 000097636347 00000000000
|
Now what is the expected output from the above 2 records?
Is it
| Code: |
CON0000000 XXX 000097636347 00000000000 6414656
ASC0000000 XXX 000097636347 00000000000 6416152
|
or
Since you have duplicates in both files do you generate a Cartesian join?
| Code: |
CON0000000 XXX 000097636347 00000000000 6414656
ASC0000000 XXX 000097636347 00000000000 6414656
CON0000000 XXX 000097636347 00000000000 6416152
ASC0000000 XXX 000097636347 00000000000 6416152
|
|
|
| Back to top |
|
|
mvmadhavi
New User
Joined: 09 Nov 2006
Posts: 21
|
|
|
|
Hi,
I have checked the cartesian join. this is not the result expected.
Expected is
File 1
000097636347FB6414656
000097636347FB6416152
000097641712FB6414657
000097641712FB6416153
000097673341 0000000
000097673462FB6415711
000097673462FB6417062
File 2
CON0000000 XXX00009763634700000000000
ASC0000000 XXX00009763634700000000000
CON0000000 XXX00009764171200000000000
ASC0000000 XXX00009764171200000000000
CON0000000 XXX00009767334100000000000
ASC0000000 XXX00009767334100000000000
CON0000000 XXX00009767346200000000000
ASC0000000 XXX00009767346200000000000
Expected
CON6414656 EAB00009763634700000000000
ASC6416152 EAB00009763634700000000000
CON6414657 EAB00009764171200000000000
ASC6416153 EAB00009764171200000000000
CON0000000 EAB00009767334100000000000
ASC0000000 EAB00009767334100000000000
CON6415711 EAB00009767346200000000000
ASC6417062 EAB00009767346200000000000 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
In addition to the sample input/output, you need to post the "rules" for processing. The rules should be presented in paragra[ph/sentence form. |
|
| Back to top |
|
|
|
|
