|
View previous topic :: View next topic
|
| Author |
Message |
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
Hi - is it possible to use JOINKEYS on joining a numeric field with a packed decimal field.
Thanks for the help. |
|
| Back to top |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| What does manuals have to say? If you're a syncsort user, see "Joining Records from Multiple Files" in item DATA-UTILITY. |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Quote: |
| is it possible to use JOINKEYS on joining a numeric field with a packed decimal field. |
Yes, but since JOINKEYS only deals with binary fields, you would have to "normalize" one of the fields first so it can be compared with the other as BI format. With DFSORT's JOINKEYS, you can do that as part of the JOINKEYS application by using an INREC statement for one of the subtasks. You don't need an extra pass to do the normalization.
If you are using DFSORT and have the Nov, 2009 PTF installed and want more information on how to do this, give a complete description of your requirement. RECFM and LRECL of the input file; starting position, length and format of the relevant fields; an example of the records in the input files and expected output. |
|
| Back to top |
|
|
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
hi Frank/Anuj - Thanks. I am a SYNCSORT user.
Looking at the manual, it seems that in the priority of control statements for SYNCSORT:
i) INCLUDE/OMIT
ii) JOINKEYS
iii) INREC
iv) SORT
v) SUM / DUPKEYS
vi) OUTREC
JOINKEYS have more priority than INREC. So, am i correct in thinking that it cannot be done in a single step? I need to write another step to reformat the records and then pass on to the JOIN step.
Thanks for your help |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Quote: |
| So, am i correct in thinking that it cannot be done in a single step? |
May be  , you did not tell us what you were trying to do. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
And the question in first post from you (is it possible to use JOINKEYS on joining a numeric field with a packed decimal field. ) is totally different than what you ask in next post
| Quote: |
| priority of control statements |
|
|
| Back to top |
|
|
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
Hi Anuj, sorry for the confusion... 
let me explain in details..
I have two files
File1
| Code: |
0001A
0002B
0003B
0004N
0005M
|
File2 (first 3 chars are packed and values same as File1)
| Code: |
.>.JOHN
.#.JIM
./.JESSIE
.?.JEREMY
.;.JACK
|
Now, I want to join these and get the output file something like
| Code: |
0001JOHN A
.
.
. and so on
|
and the question is, within a single SORT step (i think) it will not be possible to use JOINKEYS, because JOINKEYS should have same length for the keys of both files.. and INREC cannot be used in that step to reformat the packed decimal to binary or numeric prior to JOINKEYS because JOINKEYS are executed prior to INREC as per manual and also, i am not sure how can INREC work on just one file...
was i clear this time ? |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
With DFSORT's JOINKEYS, you can actually use different INREC statements to preprocess the F1 records, preprocess the F2 records, and/or post-process the joined records, so you could do what you want without extra passes. You can use JNF1CNTL for control statements to preprocess F1, JNF2CNTL for control statements to preprocess F2, and SYSIN (or DFSPARM) to postprocess the joined records. For complete details, see:
www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
AFAIK, Syncsort's JOINKEYS cannot preprocess F1 and F2 so you would need separate passes. I'm sure the Syncsort guys will correct me if I'm wrong. |
|
| Back to top |
|
|
|
|
