Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How can we have the varying lenth of key filed in join key!

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How can we have the varying lenth of key filed in join key!
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: 159
Location: Chennai

PostPosted: Fri Oct 14, 2016 8:12 pm    Post subject: Reply to: How can we have the varying lenth of key filed in join key!
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: 159
Location: Chennai

PostPosted: Fri Oct 14, 2016 8:14 pm    Post subject: Reply to: How can we have the varying lenth of key filed in join key!
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    Post subject: Re: Reply to: How can we have the varying lenth of key filed in join key!
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sat Oct 15, 2016 11:27 pm    Post subject: Reply to: How can we have the varying lenth of key filed in join key!
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    Post subject:
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: 2158
Location: @my desk

PostPosted: Sun Oct 16, 2016 3:28 am    Post subject: Reply to: How can we have the varying lenth of key filed in join key!
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am
No new posts Join and Omit records- Master File no... shajeeth DFSORT/ICETOOL 5 Tue Jan 26, 2016 8:16 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us