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

How to get matching data from two different files
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
minakshichidrawar

New User


Joined: 02 Jan 2009
Posts: 5
Location: Pune
PostPosted: Wed May 26, 2010 5:07 pm
 Reply with quote
Hi,
I m having two input files
1st file is FB, LRECL=35
& data is :

Roses 03 Red
Daisies 06 Orange
Roses 04 Pink
Daisies 02 Yellow
Roses 06 Yellow
Daisies 12 Lilac
Roses 09 Blue

2 file is FB, LRECL=30
& data is :

Red Lilies InStock
Red Roses InStock
Orange Daisies SoldOut
Pink Roses SoldOut
Yellow Daisies InStock
Yellow Roses Ordered
Lilac Daisies SoldOut
White Daisies InStock

And I want the output as :

Color Flower Status Per Pot
------- -------------- ------- -------
Lilac Daisies SoldOut 12
Orange Daisies SoldOut 6
Yellow Daisies InStock 2
Pink Roses SoldOut 4
Red Roses InStock 3
Yellow Roses Ordered 6

This output how can I get with the help of Joinkeys?


icon_question.gif
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Wed May 26, 2010 5:36 pm
 Reply with quote
Because the solution for sort related questions may vary from product to product, please ensure that you state clearly which sort product you are using.

If you are not sure, then by running a simple sort step shown below, you will be able to find out for yourself.

If the messages start with ICE then your product is DFSORT. Please also post the output of the complete line which has a message code ICE201I, as this will enable our DFSORT experts to determine which release of DFSORT that you have installed. This may also affect the solution offered.

If the messages start with WER or SYT then the product is SYNCSORT and the topic will be moved into the JCL forum by one of the moderators. Please also post the information telling which version of SYNCSORT is installed, as this may also affect the solution offered.

Code:
//SORTSTEP EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
ABC
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  SORT     FIELDS=COPY
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Wed May 26, 2010 11:20 pm
 Reply with quote
minakshichidrawar,

Since you didn't use UBB code tags, it's impossible to tell the starting positions of your fields.

Please give the starting position, length and format of each field in each input and output file.

Since you posted in the DFSORT Forum and are asking about JOINKEYS, I assume you have the Nov, 2009 DFSORT PTF that supports JOINKEYS - right?
Back to top
View user's profile Send private message
minakshichidrawar

New User


Joined: 02 Jan 2009
Posts: 5
Location: Pune
PostPosted: Thu May 27, 2010 10:54 am
 Reply with quote
Hi,

Thanks for ur prompt reply & sorry for nt telling details..
I want to sort on the fileds
For 1st file 1,15,A,20,8,A
& for 2nd file 10,15,A,1,8,A

1st file is
----+----1----+----2----+----3----+
ROSES 03 RED
DAISIES 06 ORANGE
ROSES 04 PINK
DAISIES 02 YELLOW
ROSES 06 YELLOW
DAISIES 12 LILAC
ROSES 09 BLUE

& 2nd file is
----+----1----+----2----+----3----
RED LILIES INSTOCK
RED ROSES INSTOCK
ORANGE DAISIES SOLDOUT
PINK ROSES SOLDOUT
YELLOW DAISIES INSTOCK
YELLOW ROSES ORDERED
LILAC DAISIES SOLDOUT
WHITE DAISIES INSTOCK

I tried following JCL :
//SORTJNF1 DD *
ROSES 03 RED
DAISIES 06 ORANGE
ROSES 04 PINK
DAISIES 02 YELLOW
ROSES 06 YELLOW
DAISIES 12 LILAC
ROSES 09 BLUE
/*
//SORTJNF2 DD *
RED LILIES INSTOCK
RED ROSES INSTOCK
ORANGE DAISIES SOLDOUT
PINK ROSES SOLDOUT
YELLOW DAISIES INSTOCK
YELLOW ROSES ORDERED
LILAC DAISIES SOLDOUT
WHITE DAISIES INSTOCK
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,15,A,20,8,A)
JOINKEYS FILE=F2,FIELDS=(10,15,A,1,8,A)
REFORMAT FIELDS=(F1:20,8,1,15,F2:26,10,F1:16,2)
OPTION COPY
OUTFIL REMOVECC,
HEADER2=(1:'COLOR',11:'FLOWER',26:'STATUS ',36:'PER POT',/, -
1:7'-',11:14'-',26:9'-',36:7'-'), -
BUILD=(1:1,8,11:9,15,26:24,10, -
36:34,2,ZD,M10,LENGTH=7)
/*

Bt I got the o/p as :
COLOR FLOWER STATUS PER POT
------- -------------- --------- -------

To get the o/p as I want what I ve to do?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu May 27, 2010 10:19 pm
 Reply with quote
When I run the DFSORT job you show with the input you show, I get the output you say you want:

Code:

COLOR     FLOWER         STATUS    PER POT       
-------   -------------- --------- -------       
LILAC     DAISIES        SOLDOUT        12       
ORANGE    DAISIES        SOLDOUT         6       
YELLOW    DAISIES        INSTOCK         2       
PINK      ROSES          SOLDOUT         4       
RED       ROSES          INSTOCK         3       
YELLOW    ROSES          ORDERED         6       


If you're not getting that output, then I suspect the input is really not what you've shown (different starting position somewhere), or something else is going on that you're not telling us about.

Here's what my input files look like with ubb codes to show them in a fixed font:

Code:

//SORTJNF1 DD *                               
ROSES          03  RED                       
DAISIES        06  ORANGE                     
ROSES          04  PINK                       
DAISIES        02  YELLOW                     
ROSES          06  YELLOW                     
DAISIES        12  LILAC                     
ROSES          09  BLUE                       
/*                                           
//SORTJNF2 DD *                               
RED      LILIES          INSTOCK             
RED      ROSES           INSTOCK             
ORANGE   DAISIES         SOLDOUT             
PINK     ROSES           SOLDOUT             
YELLOW   DAISIES         INSTOCK             
YELLOW   ROSES           ORDERED             
LILAC    DAISIES         SOLDOUT             
WHITE    DAISIES         INSTOCK             
/*                                           


SORTJNF1:
Field 1 starts in position 1
Field 2 starts in position 16
Field 3 starts in position 20

SORTJNF2:
Field 1 starts in position 1
Field 2 starts in position 10
Field 3 starts in position 26
Back to top
View user's profile Send private message
minakshichidrawar

New User


Joined: 02 Jan 2009
Posts: 5
Location: Pune
PostPosted: Mon May 31, 2010 11:41 am
 Reply with quote
Hi Frank Yaeger,

But the same JCL when I ran I m nt getting o/p as wat u ve shown...
Why is it so? Can U please explain me...
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: Mon May 31, 2010 11:43 am
 Reply with quote
Hello,

You need to post the complete jcl and control statements you submitted and all of the information (including messsage ids) presented by the run.
Back to top
View user's profile Send private message
minakshichidrawar

New User


Joined: 02 Jan 2009
Posts: 5
Location: Pune
PostPosted: Mon May 31, 2010 12:07 pm
 Reply with quote
Hi,
I Have submitted Following JCL :

Code:
//JKE1 EXEC PGM=SORT           
//SYSOUT DD SYSOUT=*           
----+----1----+----2----+----3-
//SORTJNF1 DD *               
ROSES          03  RED         
DAISIES        06  ORANGE     
ROSES          04  PINK       
DAISIES        02  YELLOW     
SOSES          06  YELLOW     
DAISIES        12  LILAC       
ROSES          09  BLUE       
----+----1----+----2----+----3-
/*                             
//SORTJNF2 DD *                   
RED     LILIES           INSTOCK 
RED     ROSES            INSTOCK 
ORANGE  DAISIES          SOLDOUT 
PINK    ROSES            SOLDOUT 
YELLOW  DAISIES          INSTOCK 
YELLOW  ROSES            ORDERED 
LILAC   DAISIES          SOLDOUT 
WHITE   DAISIES          INSTOCK 
/*                               
//SORTOUT DD SYSOUT=*             
//SYSIN DD *                                                     
 JOINKEYS FILE=F1,FIELDS=(1,15,A,20,8,A)                         
 JOINKEYS FILE=F2,FIELDS=(10,15,A,1,8,A)                         
 REFORMAT FIELDS=(F1:20,8,1,15,F2:26,10,F1:16,2)                 
 OPTION COPY                                                     
 OUTFIL REMOVECC,                                                 
 HEADER2=(1:'COLOR',11:'FLOWER',26:'STATUS    ',36:'PER POT',/, -
            1:7'-',11:14'-',26:9'-',36:7'-'), -                   
 BUILD=(1:1,8,11:9,15,26:24,10,   -                               
          36:34,2,ZD,M10,LENGTH=7)                               
/*                                                               


SORTJNF1:
Field 1 starts in position 1
Field 2 starts in position 16
Field 3 starts in position 20

SORTJNF2:
Field 1 starts in position 1
Field 2 starts in position 10
Field 3 starts in position 26


& I m getting o/p as :

Code:
********************************* TOP OF DATA ****
COLOR     FLOWER         STATUS    PER POT       
-------   -------------- --------- -------       
******************************** BOTTOM OF DATA **


Bt I want the o/p As :

Code:
COLOR     FLOWER         STATUS    PER POT       
-------   -------------- --------- -------       
LILAC     DAISIES        SOLDOUT        12       
ORANGE    DAISIES        SOLDOUT         6       
YELLOW    DAISIES        INSTOCK         2       
PINK      ROSES          SOLDOUT         4       
RED       ROSES          INSTOCK         3       
YELLOW    ROSES          ORDERED         6     


To get this o/p what I have to do?
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: Mon May 31, 2010 12:28 pm
 Reply with quote
Hello,

One bit at a time. . . Now for the rest that was requested:
Quote:
and all of the information (including messsage ids) presented by the run.


Also, your data for F2 does not match the JOINKEYS statement?
Back to top
View user's profile Send private message
minakshichidrawar

New User


Joined: 02 Jan 2009
Posts: 5
Location: Pune
PostPosted: Mon May 31, 2010 1:15 pm
 Reply with quote
Hi,


Quote:
Also, your data for F2 does not match the JOINKEYS statement?


Because of this I was not getting o/p as I want as I editted the i/p F2.. I got o/p as I want.

Thanks a lot icon_smile.gif
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: Mon May 31, 2010 9:05 pm
 Reply with quote
You're welcome - thank you for letting us know it is working icon_smile.gif

d
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 How to save SYSLOG as text data via P... All Other Mainframe Topics 4
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Store the data for fixed length COBOL Programming 1
No new posts Write line by line from two files DFSORT/ICETOOL 7
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us