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
 

 

Joinkeys with no key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Rajesh S
Warnings : 1

New User


Joined: 11 Jul 2007
Posts: 54
Location: Chennai

PostPosted: Tue Jan 13, 2015 3:00 pm    Post subject:
Reply with quote

Hi,

I have requirement to reformat the records as below. Both the input files are fixed length of 520.

Input file-1:


1111 RAMESH CHENNAI *#&##@ INDIA
2222 SURESH BANGALORE *$*%#*@ INDIA
3333 RAVI MUMBAI *%*%##2 INDIA
4444 AJAY KOLKATA ^&%*$(#@ INDIA


Input file-2:

VITE001 PGMR
VITE002 ASSIT
VITE002 MNGR
VITE003 PGMR

Output File:

1111 RAMESH CHENNAI PGMR INDIA
2222 SURESH BANGALORE ASSIT INDIA
3333 RAVI MUMBAI MNGR INDIA
4444 AJAY KOLKATA PGMR INDIA

I tried with below control card, it’s not working as expected. Please help me in this?

//SYSIN DD *
OPTION COPY
JOINKEYS FILE=F1,FIELDS=(520,1,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(520,1,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:1,32,F2:33,160,F1:193,428)
//*
//JNF1CNTL DD *
OPTION STOPAFT=45
INREC OVERLAY=(520:C'A')
//*
//JNF2CNTL DD *
OPTION STOPAFT=45
INREC OVERLAY=(520:C'A')
/*

Thanks in advance

Regards,
Rajesh S.
Back to top
View user's profile Send private message

Rajesh S
Warnings : 1

New User


Joined: 11 Jul 2007
Posts: 54
Location: Chennai

PostPosted: Tue Jan 13, 2015 4:18 pm    Post subject: Reply to: Join the data between two files using DFSORT
Reply with quote

To make it more clear. Below is my requirement

Input file-1:

Code:
1111    RAMESH    CHENNAI        *#&##@        INDIA
2222    SURESH    BANGALORE      *$*%#*@       INDIA
3333    RAVI      MUMBAI         *%*%##2       INDIA
4444    AJAY      KOLKATA        ^&%*$(#@      INDIA


Input file-2:

Code:
VITE001 PGMR
VITE002 ASSIT
VITE002 MNGR
VITE003 PGMR


Output file:

Code:
1111    RAMESH    CHENNAI        PGMR       INDIA
2222    SURESH    BANGALORE      ASSIT      INDIA
3333    RAVI      MUMBAI         MNGR       INDIA
4444    AJAY      KOLKATA        PGMR       INDIA


I have tried with below control card. but it's not give desire output.

Code:

//SYSIN DD *
OPTION COPY
JOINKEYS FILE=F1,FIELDS=(520,1,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(520,1,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:1,32,F2:33,160,F1:193,428)
//*
//JNF1CNTL DD *
OPTION STOPAFT=45
INREC OVERLAY=(520:C'A')
//*
//JNF2CNTL DD *
OPTION STOPAFT=45
INREC OVERLAY=(520:C'A')
/*
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Jan 13, 2015 5:36 pm    Post subject: Reply to: Joinkeys with no key
Reply with quote

If you create a key which only has one value on both files, you will get a Cartesian Join. Every record on both files will match every other.

You need to instead use a sequence number added to each record of both files, and use that as the key.

You can cut down the size/format of the data that you want with BUILD on INREC in the JNFnCNTL files, then order it with the REFORMAT statement.
Back to top
View user's profile Send private message
Rajesh S
Warnings : 1

New User


Joined: 11 Jul 2007
Posts: 54
Location: Chennai

PostPosted: Wed Jan 14, 2015 11:10 am    Post subject:
Reply with quote

Thanks Bill,

It works as you said.

Code:

//SYSIN  DD *                                         
  OPTION COPY                                         
    JOINKEYS FILE=F1,FIELDS=(621,4,A),SORTED,NOSEQCK 
    JOINKEYS FILE=F2,FIELDS=(621,4,A),SORTED,NOSEQCK 
    REFORMAT FIELDS=(F2:1,32,F1:33,160,F2:193,428)   
//*                                                   
//JNF1CNTL DD *                                       
    OPTION STOPAFT=1000                               
    INREC OVERLAY=(621:SEQNUM,4,ZD)                   
//*                                                   
//JNF2CNTL DD *                                       
    OPTION STOPAFT=1000                               
    INREC OVERLAY=(621:SEQNUM,4,ZD)                   
/*                                                   
//                                                   
Back to top
View user's profile Send private message
Rajesh S
Warnings : 1

New User


Joined: 11 Jul 2007
Posts: 54
Location: Chennai

PostPosted: Wed Jan 14, 2015 12:56 pm    Post subject:
Reply with quote

Hi,

I need to modify the control card to retain some of the record fields are in readable format from file-1. how can we do it?

Input File-1:

Code:
1111    RAMESH    CHENNAI        *#&##@        INDIA
2222    SURESH    BANGALORE      *$*%#*@       INDIA
3333    RAVI      MUMBAI         *%*%##2       INDIA
4444    HARI      NOIDA          PGMR2         INDIA
5555    AJAY      KOLKATA        ^&%*$(#@      INDIA
6666    KUMAR     PUNE           PGMR4         INDIA
7777    MAGESH    MANGALORE      MNGR2         INDIA


Input File-2:

Code:
VITE001 PGMR1
VITE002 ASSIT1
VITE003 MNGR1
VITE004 PGMR2
VITE005 PGMR3
VITE006 PGMR4
VITE007 MNGR2


Output File-3:

Code:
1111    RAMESH    CHENNAI        PGMR1      INDIA
2222    SURESH    BANGALORE      ASSIT1     INDIA
3333    RAVI      MUMBAI         MNGR1      INDIA
4444    HARI      NOIDA          PGMR2      INDIA
5555    AJAY      KOLKATA        PGMR3      INDIA
6666    KUMAR     PUNE           PGMR4      INDIA
7777    MAGESH    MAGALORE       MNGR2      INDIA
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jan 14, 2015 6:03 pm    Post subject: Reply to: Joinkeys with no key
Reply with quote

You'll have to be more explicit about what you mean. I can read all of your sample output, so what is the problem?
Back to top
View user's profile Send private message
Rajesh S
Warnings : 1

New User


Joined: 11 Jul 2007
Posts: 54
Location: Chennai

PostPosted: Wed Jan 14, 2015 10:59 pm    Post subject:
Reply with quote

Sorry Bill,
I have modify the input files as below. First field in both file are same. I want to retain the records 'ZZT' which are readable format.

Input file-1

Code:
ZZA 1111    RAMESH    CHENNAI        *#&##@        INDIA
ZZA 2222    SURESH    BANGALORE      *$*%#*@       INDIA
ZZB 3333    RAVI      MUMBAI         *%*%##2       INDIA
ZZT 4444    HARI      NOIDA          PGMR2         INDIA
ZZB 5555    AJAY      KOLKATA        ^&%*$(#@      INDIA
ZZT 6666    KUMAR     PUNE           PGMR4         INDIA
ZZT 7777    MAGESH    MANGALORE      MNGR2         INDIA


Input file -2:

Code:
ZZA VITE001 PGMR1
ZZA VITE002 ASSIT1
ZZB VITE003 MNGR1
ZZT VITE004 *&%'#@
ZZB VITE005 PGMR3
ZZT VITE006 *&%$?%
ZZT VITE007 *#@!&?
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 operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am
No new posts JOINKEYs and Variable files migusd SYNCSORT 8 Fri Jun 17, 2016 3:16 am


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