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
 

 

Full Outer Join in Sort Help Required

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

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 1:38 am    Post subject: Full Outer Join in Sort Help Required
Reply with quote

Hello,

I have two files i need to compare and join them based on key values.

Input 1
--------
Code:
04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995   
04659993ACPK0000E0304659996   
04659993ACPK0000E0204659997AB


input2
---------
Code:
04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659997AB
04659993ACPK0000E0268229953AA
04659993ACPK0000E0304659996


Required Output
------------------
Code:
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995  $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
04659993ACPK0000E0304659996  04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA

I have used the following jcl sort to get this done.
Code:

JOINKEYS FILES=F1,FIELDS=(1,10,A,18,12,A), 
                         SORTED,NOSEQCK     
JOINKEYS FILES=F2,FIELDS=(1,10,A,18,12,A), 
                         SORTED,NOSEQCK     
JOIN UNPAIRED,F1,F2                         
SORT FIELDS=COPY                             
REFORMAT FIELDS=(F1:30,F2:1,30),FILL=C'$'


After running this sort the below output has come. And its not what i have expected.

Wrong Output
---------------
Code:
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995  $$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA
04659993ACPK0000E0304659996  04659993ACPK0000E0304659996 
04659993ACPK0000E0204659997AB$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


Note: I dont want to sort my input files. This is very important. Is there any other way other than sort is also fine.

Can anyone help me in this request.

Thank You

Code'd
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Fri Mar 28, 2014 2:21 am    Post subject: Reply to: Full Outer Join in Sort Help Required
Reply with quote

If your second key is not in order, how do you expect it to work?

Why don't you want to SORT?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Mar 28, 2014 3:29 am    Post subject:
Reply with quote

Quote:
I dont want to sort my input files. This is very important. Is there any other way other than sort is also fine.


And why do you not want them to be sorted?

Try and make a use of SAS
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Mar 28, 2014 4:11 am    Post subject:
Reply with quote

See if below post makes any help,

http://www.ibmmainframes.com/about58064.html
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 28, 2014 6:34 am    Post subject:
Reply with quote

How/why do you expect to get the "desired" output given the "rules" you have specified?

If you explain what these fields are we may have a suggestion. I do not understand the situation icon_confused.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Mar 28, 2014 9:18 am    Post subject:
Reply with quote

Also the Title and the content seem to differ for me

When you don't need sort what is the expected output?
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 9:49 am    Post subject: Reply to: Full Outer Join in Sort Help Required
Reply with quote

Rohit and Bill,
My second input file will be in the provided order and it should not be sorted, so I need to match up both these files and get the desired output without missing the order of records.


Please let me know if we have any possible solution for this using SORT or ICETOOL

Thanks
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Mar 28, 2014 11:04 am    Post subject:
Reply with quote

Could you show the expected output within code tags?
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 12:01 pm    Post subject: Reply to: Full Outer Join in Sort Help Required
Reply with quote

This is my desired output.

Code:
04659993ACPK0000E0104659993AC04659993ACPK0000E0104659993AC
04659993ACPK0000E0204659617AB04659993ACPK0000E0204659617AB
04659993ACPK0000E0204659995  $$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
04659993ACPK0000E0304659996  04659993ACPK0000E0304659996
04659993ACPK0000E0204659997AB04659993ACPK0000E0204659997AB
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$04659993ACPK0000E0268229953AA


Example:

File 1 :
Code:

Company     Level     Sub Company 
A                 01         B
A                 02         C
A                 02         D
A                 03         E
A                 01         X
A                 02         Y
B                 01         H



File 2 :
Code:

Company     Level     Sub Company
A                 01         B
A                 02         D
A                 03         G
A                 02         Y
B                 01         H
C                 01         Z



i have compare these file without sorting because company and sub company.
For company A i have B is the first level sub company and C,D,E 2 and 3rd level company's for A.
Under the same A i have X as the first level company and Y as the 2nd level.

My screen has to display in the same order when i inquire "A" it should display "B" as the first level
and C,D,E as 2nd and 3rd level


This is how the screen will look like

Screen
------
Code:
A 01    B
A  -02  C
A  -02  D
A  -03  E
A 01    X
A  -02  Y


For this reason only i dont want to sort my file

and my output after comparing these two file should be

Desired Output After sort
Code:
-------------------------
File 1    File 2   

A 01  B   A  01 B
A 02  C   $$$$$$$  ( Because "C" is not present in file 2 as sub company level hence i need $ )
A 02  D   A  02 D
A 03  G   A  03 G
A 01  X   $$$$$$$  ( Because"X" is not present in file 2 as sub company level hence i need $)     
A 02  Y   A  02 Y
B 01  H   B  01 H
$$$$$$$   C  01 Z  (Because "C" is not present in file 1 as company level hence i need $)


Please help me to resolve this issue. If any other suggestion other than sort is also could help us in solving this issue.

Thank You for helping me out.

Code'd again
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Mar 28, 2014 1:53 pm    Post subject:
Reply with quote

Can you please sort the files and get the output as you expect, we will see next step how to retain the same order on your screen?

Also, please let us know what logic is place for screen?
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 2:00 pm    Post subject:
Reply with quote

Hi Their,

I mentioned the screen layout in my earlier post.

Screen
------
A 01 B
A -02 C
A -02 D
A -03 E
A 01 X
A -02 Y

For this reason only i should not sort my files.

Can you please let me know if you have any other suggestions.

Thank You
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Fri Mar 28, 2014 2:03 pm    Post subject: Reply to: Full Outer Join in Sort Help Required
Reply with quote

If your data is not in sequence on the keys then how do you export JOINKEYS to work?

If there is some strange reason that you can't SORT the data so that the keys are in sequence, then you can't use JOINKEYS for the data you have shown.

COBOL?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Mar 28, 2014 2:22 pm    Post subject:
Reply with quote

How do you get ?

Code:
Screen
------
A 01 B
A -02 C
A -02 D
A -03 E
A 01 X
A -02 Y


It basically should be,

Code:
Screen
------
A 01 B
A -02 C
A -02 D
A -03 G
A 01 X
A -02 Y


Also please tell us more on step by step approach on how the data is shown on screen? what do you do next on the output file after the outer join?
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 5:35 pm    Post subject:
Reply with quote

Please ignore my previous example you can take look at below example.

Are other example is


Technology Escalation Level Mgr.Name
Mainframe 1 A
Mainframe 2 B (COBOL)
Mainframe 2 C (Eazytrieve)
Mainframe 3 D (Eazytrieve)
Mainframe 2 Z (DB2)
Java 2 X (J2EE)

i have the technology which is again mapped to escalation level with mgr list. I cannot theese details with Technology and escalation level. Because if i sort with these keys i will get all 2nd level esclation together
which is wrong because i have 3rd level escalation for Eazytrieve.

Please let me know my example is clear.

Thank You
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Fri Mar 28, 2014 5:48 pm    Post subject: Reply to: Full Outer Join in Sort Help Required
Reply with quote

Humour me, and try this. Your F2 looks to be in sequence, but I've taken off the NOSEQCK temporarily so you can check.

Code:
 JOINKEYS FILES=F1,FIELDS=(1,10,A,18,12,A)
 JOINKEYS FILES=F2,FIELDS=(1,10,A,18,12,A),
                         SORTED
 JOIN UNPAIRED,F1,F2                         
 SORT FIELDS=COPY                             
 REFORMAT FIELDS=(F1:30,F2:1,30),FILL=C'$'


Please learn how to use the Code tags.
Back to top
View user's profile Send private message
gayathrinag

New User


Joined: 16 Oct 2008
Posts: 37
Location: chennai

PostPosted: Fri Mar 28, 2014 5:55 pm    Post subject:
Reply with quote

I did try removing the NOSEQNCK and my F2 is not in Sorted. I have given the Eazytrieve and Cobol is just convey my requirements. In my original file i dont have COBOL, EAZYTRIEVE rather i only have Mgr Name and the Escaltion with Technology called mainframe or Java.

Thank You
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Mar 28, 2014 11:29 pm    Post subject: Re: Reply to: Full Outer Join in Sort Help Required
Reply with quote

gayathrinag wrote:

File 1 :
Code:

Company     Level     Sub Company 
A                 01         B
A                 02         C
A                 02         D
A                 03         E
A                 01         X
A                 02         Y
B                 01         H



File 2 :
Code:

Company     Level     Sub Company
A                 01         B
A                 02         D
A                 03         G
A                 02         Y << How does it go to 2nd group?
B                 01         H
C                 01         Z


Desired Output After sort
Code:
-------------------------
File 1    File 2   

A 01  B   A  01 B
A 02  C   $$$$$$$  ( Because "C" is not present in file 2 as sub company level hence i need $ )
A 02  D   A  02 D
A 03  G   A  03 G
A 01  X   $$$$$$$  ( Because"X" is not present in file 2 as sub company level hence i need $)     
A 02  Y   A  02 Y
B 01  H   B  01 H
$$$$$$$   C  01 Z  (Because "C" is not present in file 1 as company level hence i need $)



How do you distinguish file 2 records that belong to a different group? Is 3 the highest sub level?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Sat Mar 29, 2014 1:17 am    Post subject:
Reply with quote

I think of one approach, see if this looks as expected.

1) Assign SEQNUM to all the records at last in the input files and get the new output files
2) Then do a sorting based on the key provided by you and make a full outer join as per your requirement
3) The output of full outer join again needs to be sorted based upon the step #1 i.e. based on the SEQNUM to retain back your original order.

Kolusu/Bill, Need your thoughts on this, I might be missing something in this approach.
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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