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
 

 

Need to remove Leading zeros while using Joinkeys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
SHAILESH OZA

New User


Joined: 10 Jun 2005
Posts: 21
Location: Mumbai

PostPosted: Sat Feb 11, 2012 8:05 pm    Post subject: Need to remove Leading zeros while using Joinkeys
Reply with quote

I have two files

1. File1 -is sent by Business Unit.
2. File2- is unloaded from DB2 Tables
I am copying Matching Records into third file by using Joinkeys. But facing problem while matching the dates.

Date Field in file1 comes with Leading Zero in the format 01/01/2012 -
X(10).
and from the file2 which is from DB2 table, It comes as 1/1/2012

Due to leading Zero from file1, the record is not getting matched. Is it possible to remove the leading zero in Joinkey while matching the record.

I used the below control card as mentioned below,

JOINKEYS FILE=F1,FIELDS=(526,10,A)
JOINKEYS FILE=F2,FIELDS=(526,10,A)
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7306

PostPosted: Sat Feb 11, 2012 9:13 pm    Post subject: Reply to: Need to remove Leading zeros while using Joinkeys
Reply with quote

This should give you a start. Check back later for a better solution from elsewhere.

The adjustment of the date is done in the JNF1CNTL file. I've just used DD * to test with dates alone, so you'll have to adjust the positions for your dates.

The OPTION COPY, JOIN UNPAIRED, REFORMAT and BUILD are just for testing the sample, you'll obviously have your own code for what you need.


Code:
//DATEZERO EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  OPTION COPY
  JOINKEYS F1=IN1,FIELDS=(1,10,A)
  JOINKEYS F2=IN2,FIELDS=(1,10,A)
  JOIN UNPAIRED
  REFORMAT FIELDS=(F1:1,10,F2:1,10,?)
  INREC BUILD=(1,10,X,11,10,X,21,1)
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'0'),
           OVERLAY=(1:X),HIT=NEXT),
        IFTHEN=(WHEN=(4,1,CH,EQ,C'0'),
           OVERLAY=(4:X),HIT=NEXT),
        IFTHEN=(WHEN=ANY,
           OVERLAY=(1:1,10,SQZ=(SHIFT=LEFT)))
//*
//SORTIN DD *
//IN1 DD *
01/01/2012
01/12/2012
12/01/2012
13/12/2012
//IN2 DD *
1/1/2012
1/12/2012
12/1/2012
13/12/2012


Output:

Code:
1/1/2012   1/1/2012   B
1/12/2012  1/12/2012  B
12/1/2012  12/1/2012  B
13/12/2012 13/12/2012 B
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Feb 13, 2012 10:20 pm    Post subject:
Reply with quote

Ideally I would prefer to perform the comparison on CCYY/MM/DD format instead of DD/MM/CCYY format, so that dates are sorted in ascending sequence. Use Parse to build the dates in CCYY/MM/DD format.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Tue Feb 14, 2012 4:16 pm    Post subject:
Reply with quote

Skolusu wrote:
Ideally I would prefer to perform the comparison on CCYY/MM/DD format instead of DD/MM/CCYY format, so that dates are sorted in ascending sequence. Use Parse to build the dates in CCYY/MM/DD format.


You can use the below given sample code. Please note that rather than providing you with the logic to remove the leading zeroes from the input1, I am suggesting you to add leading zeroes to input2.

Code:
  SORT FIELDS=COPY                           
  OUTREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=8), 
              %02=(ENDBEFR=C'/',FIXLEN=2), 
              %03=(ENDBEFR=C'/',FIXLEN=2), 
              %04=(ENDBEFR=C'/',FIXLEN=2)),
       BUILD=(%01,9:%02,SFF,EDIT=(TT),C'/',
              12:%03,SFF,EDIT=(TT),C'/',   
              15:%04,SFF,EDIT=(TT))         
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Feb 14, 2012 10:51 pm    Post subject:
Reply with quote

Nikhil Jain wrote:

You can use the below given sample code. Please note that rather than providing you with the logic to remove the leading zeroes from the input1, I am suggesting you to add leading zeroes to input2.

Code:
SORT FIELDS=COPY                           
OUTREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=8), 
              %02=(ENDBEFR=C'/',FIXLEN=2), 
              %03=(ENDBEFR=C'/',FIXLEN=2), 
              %04=(ENDBEFR=C'/',FIXLEN=2)),
       BUILD=(%01,9:%02,SFF,EDIT=(TT),C'/',
              12:%03,SFF,EDIT=(TT),C'/',   
              15:%04,SFF,EDIT=(TT))         


Nikhil Jain,

Did you test your control cards? I am not sure as to where you have a "|" delimiter in the sample input. Why are you editing the date to be just 8 bytes instead of 10 ?
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Wed Feb 15, 2012 6:49 pm    Post subject:
Reply with quote

Skolusu wrote:
Nikhil Jain,

Did you test your control cards? I am not sure as to where you have a "|" delimiter in the sample input. Why are you editing the date to be just 8 bytes instead of 10 ?


Apologies for the confusion Skolusu. I have only provided the sample code to give the user an idea.I believe with slight modification he should be able to customise it to suit his requirement. The objective was to instigate the TS to give it a thought.
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 Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am
No new posts Remove junk values in a file and rite... KP1125 DFSORT/ICETOOL 2 Wed Jan 25, 2017 9:58 pm
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm


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