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

Synsort shows on the same outfile as the joined records


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Sat May 21, 2011 1:36 am
Reply with quote

i have two files to join. But I what the unjoined records to show on the same outfile as the joined records. I have used join before but have not done needed this type of output..... any done this or can you help?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat May 21, 2011 10:00 am
Reply with quote

Hello,

Show a bit of sample input from both files that contains some matches and some non-matches and the output you want when this sample data is processed.

Mention the recfm and lrecl of all files.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon May 23, 2011 4:03 pm
Reply with quote

ken herbolic wrote:
But I what the unjoined records to show on the same outfile as the joined records.
Alos, tell us from which file you need the "un-joined" records, first file, second file or both?
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 5:33 pm
Reply with quote

Hi:
The input J1 records has an account number in bytes 1 to 9

The input file J2 has an account number also in bytes 1 to 9

Matched output will be done on account number

Non matched output will be accounts numbers that do not matched from J1 to J2.

The data that is in bytes 10 to 90 I know how to do the reformatting.

LRECL on both files is 90.

Thank you,
Ken
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon May 23, 2011 6:41 pm
Reply with quote

Hi Ken,

Can you please let us know what is the output that needs to be written to the output file? Do you need data from file 1 or file 2?

It would be better if you can explain the requirement with some sample data?
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 6:46 pm
Reply with quote

Hi:
I need all the data from both J1 an J2.
All account number will be on J1 but some could be missing on J2.
The first 36 bytes of J1 will be displayed first on the output file then bytes 10 to 45 of J2 display on the same output line os J1 right after it.

make sense
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 782
Location: Pennsylvania

PostPosted: Mon May 23, 2011 7:21 pm
Reply with quote

Try using - JOIN UNPARED,F1

That along with what you already know should do it for you.
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 7:28 pm
Reply with quote

thank you !!!!

I will give it a go
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 8:33 pm
Reply with quote

That worked but i now have two account # columns. I would like to show just one account # column. do you know how I could get the account numbers in account column 2 to display in account column 1 then eliminate account column 2.... here is a smaple of the output I have now and the code used to get this far:

Accout # Account #
000000001 001
000000002 002
000010359 258 000010359 258
000010502 2 000010502 2
000010570 11 000010570 11
000010588 90 000010588 90
000010596 2 000010596 2
000023868 15017 000023868 14814

Code:
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,9,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,9,28,11,F2:1,9,45,08)
SORT FIELDS=COPY
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 8:39 pm
Reply with quote

Sorry that output did not format correctly here is another try:

Account # Account #
000000001 001
000000002 002
000010359 258 000010359 258
000010502 2 000010502 2
000010570 11 000010570 11
000010588 90 000010588 90
000010596 2 000010596 2
000023868 15017 000023868 14814
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 782
Location: Pennsylvania

PostPosted: Mon May 23, 2011 8:40 pm
Reply with quote

Above you said:
Quote:
All account number will be on J1 but some could be missing on J2.


So:
Code:
JOIN UNPAIRED,F1,F2


Should be
Code:
JOIN UNPAIRED,F1


And:
Code:
REFORMAT FIELDS=(F1:1,9,28,11,F2:1,9,45,08)


Should be:
Code:
REFORMAT FIELDS=(F1:1,9,28,11,F2:45,08)
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 8:40 pm
Reply with quote

sorry cannot get the spacing to stay once submitted
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 782
Location: Pennsylvania

PostPosted: Mon May 23, 2011 8:41 pm
Reply with quote

Click the Code button

Paste in you data

Click the Code button
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 8:48 pm
Reply with quote

I used dashes to format the data .... As i metioned is there anyway to get the account numbers form F2 to display in the F1 column...then delete the account #s in the F2 column


Code:
Account #F1 --------Account #F2
000000001 ---001
-----------------------000000002 ---002
000010359 ---258 --000010359 ---258
000010502 ------2 --000010502 -----2
000010570 -----11 --000010570 ---11
000010588 -----90 --000010588 ---90
000010596 ------2 --000010596 -----2
000023868 15017 --000023868 14814

Here is the code that is giving me the above results:
Code:
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,9,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,9,28,11,F2:1,9,45,08)
SORT FIELDS=COPY

Thank you !!!
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 782
Location: Pennsylvania

PostPosted: Mon May 23, 2011 8:52 pm
Reply with quote

Did you read my suggestions above?
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 8:53 pm
Reply with quote

Code:

Account #F1           Account #F2
000000001     001
000000002     002
000010359     258   000010359    258
000010502        2   000010502        2
000010570      11   000010570       11
000010588      90   000010588       90
000010596        2   000010596        2
000023868 15017   000023868 14814
[/code]
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 9:02 pm
Reply with quote

Yes ...just tried that now and it is much better thanks!!!..... the output now displays records on F1 that are not on F2 and I have one account number column......Now the only thing left missing is there are records on F2 that are not on F1. These records are not displaying on the output and I need them too. I need to display all records from both files, matched on one line and unmatched on another as the code is doing now for F1.

Is this possiable to do ?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 782
Location: Pennsylvania

PostPosted: Mon May 23, 2011 9:12 pm
Reply with quote

Earlier you said
Quote:
All account number will be on J1 but some could be missing on J2.


Now you are saying
Quote:
Now the only thing left missing is there are records on F2 that are not on F1. These records are not displaying on the output and I need them too.


My suggestions was for the first requirement, not a changing one.

Sorry, but I do not have the time to code this for you at this point.

There are examples on this forum.

Search for: JOIN UNPARED,F1,F2

Yes, you need to use this one since your requirement has changed.
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 9:18 pm
Reply with quote

thank you...
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Mon May 23, 2011 9:38 pm
Reply with quote

Hi.... can anyone else help on this request...I searched but cannot find the requirement I need. Again I have two files. I need to display the account number from both files just once. I need to display all records from both files, matched and unmatched. this is want has been suggested so far and is almost there:
JOINKEYS FILE=F1,FIELDS=(1,9,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,9,28,11,F2:45,08)
SORT FIELDS=COPY

Here is the output...notice no account number displays for the F2 record.
Code:

000000001     001         
                       002
000010359     258      258
000010502       2        2
000010570      11       11
000010588      90       90
000010596       2        2
000023868   15017    14814
Back to top
View user's profile Send private message
DB2 Guy

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Tue May 24, 2011 8:59 pm
Reply with quote

It would be nice if you show us some sample input records frm both the file and the expected output from that.
Back to top
View user's profile Send private message
ken herbolic

New User


Joined: 24 Jan 2011
Posts: 16
Location: buffalo

PostPosted: Tue May 24, 2011 9:20 pm
Reply with quote

thanks for all the help....sync sort support has shown me the problem with my code....the below is a working sample for anyone that may have a simiplar need.
Code:

JOINKEYS FILE=F1,FIELDS=(1,9,A)                               
JOINKEYS FILE=F2,FIELDS=(1,9,A)                               
JOIN UNPAIRED                                                 
REFORMAT FIELDS=(F1:1,9,10,51,F2:1,9,10,58),FILL=X'40'         
OUTREC IFTHEN=(WHEN=(1,1,BI,EQ,X'40'),BUILD=(61,9,51X,70,58)),
       IFTHEN=(WHEN=(NONE),BUILD=(1,60,70,58))                 
SORT FIELDS=COPY                                               
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts how to keep unpaired records with REPRO. JCL & VSAM 9
No new posts Dataset size increase on adding 1 byt... DFSORT/ICETOOL 8
No new posts Removal of date value records DFSORT/ICETOOL 12
No new posts Concurrent writing records to VSAM fr... JCL & VSAM 9
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
Search our Forums:

Back to Top