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
 

 

Merge two records without a common field
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 10:58 am    Post subject: Merge two records without a common field
Reply with quote

Hi
This is my case. there are two input files
FILE1:
------------
lno offset
1 20
5 5
5 4
1 200
3 10
FILE2:
------------
name
acct-no
ph-no
code
address
city
1.In FILE1 field1(LNO) length is 12 and field2(OFFSET) starts from position 13.
2.In FILE2 NAME field starts from position 13. Upto pos 12 it is space.

Expected OUTPUT:
lno name offset
1 acct-no 20
5 ph-no 5
5 code 4
1 address 200
3 city 10

In the output Field1 and Field3 are from FILE1 and Field2 is from FILE2.

Please someone give a solution...
[/quote]
Back to top
View user's profile Send private message

smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 11:03 am    Post subject:
Reply with quote

wud u please paste the exact data using the code tag
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 11:15 am    Post subject:
Reply with quote

FILE1:
Code:

----+----1----+----2----+----3----+----4
----------- ----------------------------
COPYBOOK :                             
LEVELNAME              GROUP      OFFSET
01                     GROUP      0     
05                     -          6     
05                     -          6     
----------- ----------------------------
COPYBOOK :                             
LEVELNAME              GROUP      OFFSET
01                     GROUP      0     
05                     -          6     
05                     -          6     
----------- ---------------------------- 


FILE2:
Code:

----+----1----+----2----+----3----+----4----+
            ------------------------------   
             AE12ATDT                       
            FIELDNAME                       
            AE12AT-PRESENTMENT-RECORD.       
            AE12AT-PRES-ACCT-NBR             
            AE12AT-PRES-ACCT-NBR             
            ------------------------------   
             AE1240DT                       
            FIELDNAME                       
            AE1240-PRESENTMENT-RECORD.       
            AE1240-PRES-ACCT-NBR             
            AE1240-PRES-ACCT-NBR             
            ------------------------------     


In FILE1 group and offset together is one field.
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:00 pm    Post subject:
Reply with quote

Code:
output :

----------- ------------------------------ ----------------------------
COPYBOOK :   AE12ATDT                                                 
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE12AT-PRESENTMENT-RECORD.                GROUP      0     
05          AE12AT-PRES-ACCT-NBR                      -          6     
05          AE12AT-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------
COPYBOOK :   AE1240DT                                                 
LEVELNAME   FIELDNAME                                 GROUP      OFFSET
01          AE1240-PRESENTMENT-RECORD.                GROUP      0     
05          AE1240-PRES-ACCT-NBR                      -          6     
05          AE1240-PRES-ACCT-NBR                      -          6     
----------- ------------------------------ ----------------------------



job :
Code:
//S1   EXEC  PGM=SORT                             
//SYSOUT DD SYSOUT=*                               
//SORTJNF1 DD *                                   
----------- ----------------------------           
COPYBOOK :                                         
LEVELNAME              GROUP      OFFSET           
01                     GROUP      0               
05                     -          6               
05                     -          6               
----------- ----------------------------           
COPYBOOK :                                         
LEVELNAME              GROUP      OFFSET           
01                     GROUP      0               
05                     -          6               
05                     -          6               
----------- ----------------------------           
/*                                                 
//SORTJNF2 DD *                                   
            ------------------------------         
             AE12ATDT                             
            FIELDNAME                             
            AE12AT-PRESENTMENT-RECORD.             
            AE12AT-PRES-ACCT-NBR               
            AE12AT-PRES-ACCT-NBR               
            ------------------------------     
             AE1240DT                           
            FIELDNAME                           
            AE1240-PRESENTMENT-RECORD.         
            AE1240-PRES-ACCT-NBR               
            AE1240-PRES-ACCT-NBR               
            ------------------------------     
/*                                             
//SORTOUT DD DSN=TEMP.TLM.TEST.DEL9,           
//        DISP=(NEW,CATLG),UNIT=DISK,           
//        SPACE=(27998,(400,100),RLSE)         
//JNF1CNTL DD *                                 
   INREC OVERLAY=(81:SEQNUM,8,ZD)               
//JNF2CNTL DD *                                 
   INREC OVERLAY=(81:SEQNUM,8,ZD)               
//SYSIN DD *                                   
   JOINKEYS FILES=F1,FIELDS=(81,8,A),SORTED     
   JOINKEYS FILES=F2,FIELDS=(81,8,A),SORTED     
   REFORMAT FIELDS=(F1:1,12,F2:13,31,F1:13,31) 
   OPTION COPY



is this wat you want ?
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:14 pm    Post subject:
Reply with quote

Thank you.. this is the output which I require..
I tried using the following lines of code:
Code:

JOINKEYS FILE=F1,FIELDS=(1,11,A)           
JOINKEYS FILE=F2,FIELDS=(1,11,A)           
JOIN UNPAIRED F1,F2                         
REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)
SORT FIELDS=COPY                           


I got SORTJNF1 NOT DEFINED error. what mistake I have made? and can you explain the solution given by you[/code]
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:16 pm    Post subject:
Reply with quote

paste the complete STEP and the ERROR message
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:18 pm    Post subject:
Reply with quote

The logic is simple :
and sequence numbers to both files and then join then using the seqnum icon_smile.gif

u can apply the same logic in ICETOOL using using SPLICE WITHANY
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:35 pm    Post subject:
Reply with quote

icon_smile.gif thanks a lot..
Code:

//MERGE    EXEC PGM=SORT,COND=(0,NE)                       
//SYSPRINT DD SYSOUT=*                                     
//SYSOUT   DD SYSOUT=*                                     
//IN01     DD DSN=BPTN.RTD.S679353.REPORT.TEST1.D,DISP=SHR 
//IN02     DD DSN=BPTN.RTD.S679353.REPORT.TEST2.D,DISP=SHR 
//SORTOUT  DD DSN=BPTN.RTD.S679353.REPORT.TEST3.D,         
//            DISP=(NEW,CATLG,DELETE),                     
//            UNIT=SYSOUT,                                 
//            SPACE=(CYL,(5,10),RLSE),                     
//            DCB=(RECFM=FB,LRECL=600,BLKSIZE=0)           
//SYSIN    DD *                                             
  JOINKEYS FILES=F1,FIELDS=(1,11,A)                         
  JOINKEYS FILES=F2,FIELDS=(1,11,A)                         
  JOIN UNPAIRED F1,F2                                       
  REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)             
  SORT FIELDS=COPY                                         


error:
Code:

ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AN
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 02:42 ON WED SE
            JOINKEYS FILES=F1,FIELDS=(1,11,A)                                   
            JOINKEYS FILES=F2,FIELDS=(1,11,A)                                   
            JOIN UNPAIRED F1,F2                                                 
            REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)                       
            SORT FIELDS=COPY                                                   
ICE056A 2 SORTJNF1 NOT DEFINED                                                 
ICE751I 0 C5-K51707 C6-K51707 C7-K54603 E7-K51707                               
ICE052I 3 END OF DFSORT                                                         
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:42 pm    Post subject:
Reply with quote

I fixed it ..I gave my ddnames

JOINKEYS F1=IN01,FIELDS=(1,11,A)
JOINKEYS F2=IN02,FIELDS=(1,11,A)
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 12:43 pm    Post subject:
Reply with quote

Quote:
//IN01 DD DSN=BPTN.RTD.S679353.REPORT.TEST1.D,DISP=SHR
//IN02 DD DSN=BPTN.RTD.S679353.REPORT.TEST2.D,DISP=SHR


as the trace suggest , "SORTJNF1 NOT DEFINED "

you have 2 options :
either change ddname IN01 to SORTJNF1 and IN02 to SORTJNF2

or
Code:
//SYSIN    DD *                                             
  JOINKEYS F1=IN01,FIELDS=(1,11,A)                         
  JOINKEYS F2=IN02,FIELDS=(1,11,A)                         
  JOIN UNPAIRED F1,F2                                       
  REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)             
  SORT FIELDS=COPY   
[/code]
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 12:59 pm    Post subject:
Reply with quote

thank you icon_smile.gif
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 4:14 pm    Post subject:
Reply with quote

Can we join without generating sequence number?
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 4:49 pm    Post subject:
Reply with quote

to merge to records there has to be something comon

how do we join two records if there is nothing in common ?
Unles u write a cobol code and read both files together one records at a time and join them
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Wed Sep 15, 2010 4:55 pm    Post subject:
Reply with quote

that is how the requirement is.. need merge all records and fromat it
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 5:17 pm    Post subject:
Reply with quote

explain logically how do u merge these records

1st record of file 1 with 1st records of file 2

nth rec of file 1 with nth record of file 2 ... right ...

u have not explained clearly , u sak something and then use exactly tht opposite ...
u have used "JOIN UNPAIRED F1,F2"

where as ur requirement was a simple merge ???
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Sep 15, 2010 10:39 pm    Post subject:
Reply with quote

Thoulath,

Note also that

Code:

  JOIN UNPAIRED F1,F2


is missing the comma after UNPAIRED, so it is actually interpreted as:

Code:

  JOIN UNPAIRED


rather than:

Code:

  JOIN UNPAIRED,F1,F2
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Thu Sep 16, 2010 2:08 pm    Post subject:
Reply with quote

my requirement is to merge two files as mentioned in the beginning. and that merging is to be done without generating any sequence number. like a one to one mapping between two input files.

I dont know much about sorting and icetool in jcl..I just tried using the below code to know whether it will give me the output. while running it I got the "SORTJNF1 NOT DEFINED " error. So asked you the reason. my requirement is not based on the code below. It was a trial given by me.
Code:
//SYSIN DD *
JOINKEYS F1=IN01,FIELDS=(1,11,A)
JOINKEYS F2=IN02,FIELDS=(1,11,A)
JOIN UNPAIRED F1,F2
REFORMAT FIELDS=(F1:1,11,F2:13,30,F1:13,557)
SORT FIELDS=COPY
[/code]
Back to top
View user's profile Send private message
thoulath

New User


Joined: 15 Sep 2010
Posts: 18
Location: chennai

PostPosted: Thu Sep 16, 2010 2:12 pm    Post subject:
Reply with quote

Now there is a change in the requirement. The second file is of length 30.As mentioned before it doesnt have spaces initially. Totally it has only the 30 bytes fieldname.

Now the file2 is like shown below:
Code:

----+----1----+----2----+----3
******************************
------------------------------
 AE12ATDT                     
FIELDNAME                     
AE12AT-PRESENTMENT-RECORD.   
AE12AT-PRES-ACCT-NBR         
AE12AT-PRES-ACCT-NBR         
------------------------------
 AE1240DT                     
FIELDNAME                     
AE1240-PRESENTMENT-RECORD.   
AE1240-PRES-ACCT-NBR         
AE1240-PRES-ACCT-NBR         
------------------------------
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Thu Sep 16, 2010 4:24 pm    Post subject:
Reply with quote

Quote:
my requirement is to merge two files as mentioned in the beginning. and that merging is to be done without generating any sequence number. like a one to one mapping between two input files.


adding a sequence number is essential to do one to one mapping , and what difference does it make as it is going to be removed from your output once mapping is done ???



Quote:
It was a trial given by me.

y give trials and test y patience , why not mention the actual requirement in the first place ...........
[/code]
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Sep 16, 2010 8:03 pm    Post subject:
Reply with quote

thoulath,

smijoss provided 95% percent of what you need in his earlier post

You now only need to do 5% which consists of:

1) Understand that the sequence number is only temporary. It is not part of the output, but provides the mapping needed to satisfy the 'requirement'

2) Learn more about the sort product in your shop, if you plan of using it.

3) Be able to modify the solution provided based on your changing requirement

4) Collect 100% of the money (and glory) for doing 5% of the work
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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