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

How can we have the varying lenth of key filed in join key!
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India
PostPosted: Fri Oct 14, 2016 7:31 pm
 Reply with quote
Hi Folks,

I've come across once scenario while using Join keys :
problem statement :Compare two different length of files(length of 10 & 18), write all key matched records from dataset-2 against dataset-1 into the one output data set.

Key Filed, would get vary from 1 to till 5 length from two data sets records.

Input records :

dataset -1
Code:
03...F51CG
03...F54TA
03...G11AJ
03...G11DD
03...G12AC
03...G12AE

Note :.... are PD numbers

dataset 2
Code:
B4               +1.33             +0.01097
B6               +1.59             +0.00327
B9               +1.69             +0.00406
F1               +1.49             +0.00502
F3               +1.77             +0.00865
F4               +1.57             +0.00512
F5               +1.52             +0.00462
F7               +1.38             +0.01356
F8               +1.55             +0.03842
G1               +1.82             +0.01564
G2               +2.54             +0.01105
G3               +1.42             +0.03960
G4               +1.36             +0.01124
G5               +1.47             +0.01400
G6               +1.90             +0.01696
G7               +2.01             +0.02553


Code:
//SPLIT01   EXEC PGM=SORT,COND=(4,LT)                             
//SYSOUT    DD SYSOUT=*                                           
//SORTJNF1  DD DSN=JKT.VXJJ0TFU.UPLOAD.FILE.P,DISP=SHR           
//SORTJNF2  DD DSN=JKT.SYST.CUSTREND.NONPROM(0),DISP=SHR         
//SORTOUT   DD DSN=JKT.SYST.CUSTREND.NONPROM.TRIAL,               
//             DISP=(NEW,CATLG,DELETE),                           
//             SPACE=(CYL,(1,1),RLSE),                           
//             MGMTCLAS=MCNEVER,UNIT=DASD,                       
//             DCB=(DSORG=PS,RECFM=FB,LRECL=18)                   
//SYSIN     DD *                                                 
  JOINKEYS FILE=F1,FIELDS=(6,1,A)                                 
  JOINKEYS FILE=F2,FIELDS=(1,1,A)                                 
  REFORMAT FIELDS=(F2:1,18)                                       
  SORT FIELDS=COPY                                               
/*                                                               
//*     


from above its giving duplicate rows in output data sets

Output :

Code:
F1               +1.49             +0.00502
F3               +1.77             +0.00865
F4               +1.57             +0.00512
F5               +1.52             +0.00462
F7               +1.38             +0.01356
F8               +1.55             +0.03842
F1               +1.49             +0.00502
F3               +1.77             +0.00865
F4               +1.57             +0.00512
F5               +1.52             +0.00462
F7               +1.38             +0.01356
F8               +1.55             +0.03842
G1               +1.82             +0.01564
G2               +2.54             +0.01105
G3               +1.42             +0.03960
G4               +1.36             +0.01124


Problem here is duplicates and incorrect mapping for example : dataset records is G1AJJ provided, basicaly it find first letter match from second file but its exact match would be G1.

what exactly i want is that is there any way to put varying key size while matching since this key filed expected to be varry from 1 to 5 length

Thanks for all in advance !!

Note - anyway im writing COBOL pgm to handle this but i just curios to know if any other way of doing it icon_smile.gif

CODE' D
help people who spend their time on Your issues
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai
PostPosted: Fri Oct 14, 2016 8:12 pm
 Reply with quote
You are matching just one byte of record in 6th position of the first dataset with
Code:

F
F
G
G
G
G


one byte of record in the first position of the second dataset

Code:

B
B
B
F
F
F
F
F
F
G
G
G
G
G
G
G


I think you need to match two bytes of record

Code:

  JOINKEYS FILE=F1,FIELDS=(6,2,A)                                 
  JOINKEYS FILE=F2,FIELDS=(1,2,A)
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai
PostPosted: Fri Oct 14, 2016 8:14 pm
 Reply with quote
Note : If your keys are in ascending order, then you may use SORTED,NOSEQCK to avoid Sorting.

Code:

  JOINKEYS FILE=F1,FIELDS=(6,2,A),SORTED,NOSEQCK                                 
  JOINKEYS FILE=F2,FIELDS=(1,2,A),SORTED,NOSEQCK
Back to top
View user's profile Send private message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India
PostPosted: Sat Oct 15, 2016 10:32 pm
 Reply with quote
magesh23586 wrote:
Note : If your keys are in ascending order, then you may use SORTED,NOSEQCK to avoid Sorting.

Code:

  JOINKEYS FILE=F1,FIELDS=(6,2,A),SORTED,NOSEQCK                                 
  JOINKEYS FILE=F2,FIELDS=(1,2,A),SORTED,NOSEQCK


Thank You So much Magesh Sir icon_smile.gif

Sir, Actaully some times first file would be having data with single length, that time it wont match but it expected to have match with any of record with same starting letter.

for Eq :

File 1: G

File 2: G1FGH

with key length two this will not work!

Thanks
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Oct 15, 2016 11:27 pm
 Reply with quote
Post your full rules for matching. We waste time just guessing. Include representative sample values and expected output.
Back to top
View user's profile Send private message
Gunapala CN

New User


Joined: 13 Oct 2016
Posts: 16
Location: India
PostPosted: Sat Oct 15, 2016 11:53 pm
 Reply with quote
Sure, Definitely

File 1 Records would be :
Code:
F2
G1
HJ
F1GHL
G2456


File 2 records would be :
Code:
F1         1.5
F            2
HL126   2.1
G            3.1
G2HMN  4.7
G2456      3.5
G1QTY     4.9

EXPPECTED OUTPUT AFTER COMPRIOSN WOULD BE:
Code:
F            2
G1QTY  4.9
F1           1.5
G2456    3.5


Basically file 1 records should try to match same length of key in file 2 if not present with same key then try to find with length 4, like this till length 1 it has to match!!
Thanks you

CODE' D
learn to help people who spend time on Your issues
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Sun Oct 16, 2016 3:28 am
 Reply with quote
Are the keys unique in either of the input files?
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 -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts Synctool-dynamic split job for varyin... JCL & VSAM 7
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
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