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
 

 

How to get matching data from two different files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How to get matching data from two different files
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: 8593
Location: Back in jolly old England

PostPosted: Wed May 26, 2010 5:36 pm    Post subject:
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 Moderator


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

PostPosted: Wed May 26, 2010 11:20 pm    Post subject:
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    Post subject: Reply to: How to get matching data from two different files
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 Moderator


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

PostPosted: Thu May 27, 2010 10:19 pm    Post subject:
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    Post subject: Reply to: How to get matching data from two different files
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

Site Director


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

PostPosted: Mon May 31, 2010 11:43 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon May 31, 2010 12:28 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon May 31, 2010 9:05 pm    Post subject: Reply to: How to get matching data from two different files
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    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 Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm


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