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

Need to remove Leading zeros while using Joinkeys


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Feb 11, 2012 9:13 pm
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
No new posts Keep leading zero(s) after convert fl... SYNCSORT 7
Search our Forums:

Back to Top