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
 

 

Need help in Joins

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Wed Oct 30, 2013 10:11 pm    Post subject: Need help in Joins
Reply with quote

Hi Team,

I am facing a problem when working with joins. Let me tell my requirement first

Requirement: To compare two files based on a Key and add the code present in last 2 columns of one file to other file

Here is the code which i am using

LRECL of F1 is 569
LRECL of F2 is 355

Code:
JOINKEYS FILE=F1,FIELDS=(9,7,A)  --> First file Key is in 9th position                               
JOINKEYS FILE=F2,FIELDS=(12,7,A) --> Second file Key is in 12th Position                               
JOIN UNPAIRED,F1 --> Wanted only Paired records from F1 as well as Unpaired from F1                                               
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'                   
SORT FIELDS=(1,567,CH,A)  --> Trying to eliminate Duplicates if any                                       
SUM FIELDS=NONE                                                 
INREC IFTHEN=(WHEN=((1,1,BI,NE,X'FF'),AND,(9,7,CH,EQ,581,7,CH)),
        BUILD=(1:1,567,568:923,2)),--> adding the last 2 digits from F2 to F1 if a match is found                             
      IFTHEN=(WHEN=NONE,                                         
        BUILD=(1:1,569))--> Write the record as it is from F1 when no match is found 


Now i had a scenario where F2 is having 2 entries for a particular Key (say abcdefg) One record having code in last 2 columns and another one having spaces

After matching is done i am unable to retrieve the code to the last 2 columns of F1, Since it is picking the second record of F2 where the last 2 columns is blank

Simple example for better understanding:

F1

Code:
ab  spaces
cd  spaces
ef  spaces

F2
Code:
ab 01
ab spaces
cd 02
ef  03


After matching i should get the following result
Code:
ab  01
cd  02
ef  03


But My output file is looking like

Code:
ab  spaces --> i want the code to be added
cd  02
ef  03


Note: F1 is having a Unique record for Particular Key
F2 may have more than one record for a particular key

Its like One to Many mapping

Please help me in this requirement. Correct me if i was going wrong some where

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

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Oct 30, 2013 11:02 pm    Post subject: Reply to: Need help in Joins
Reply with quote

Is that the full range of possible duplicates on file two? If not, give a representative sample please.
Back to top
View user's profile Send private message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Wed Oct 30, 2013 11:21 pm    Post subject:
Reply with quote

Hi Bill

F1 will be having a unique record for a particular Key

Where as F2 will have more than One record for a particular Key

I tried to eliminate duplicates because there is a chance of multiple matching
results when we are trying to compare

I mean to say we may get 2 matches in File2 when we are comparing a single record in file1

Ultimate Goal: My ultimate goal is to pick the last 2 columns of File2 and add it in file 1 if the key matches other wise write the record as it is


Please let me know if you need more information
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Oct 30, 2013 11:42 pm    Post subject: Re: Reply to: Need help in Joins
Reply with quote

Bill Woodger wrote:
Is that the full range of possible duplicates on file two? If not, give a representative sample please.


What I want is some sample input showing some combinations od duplicates and what you want for your output for each example.

Why are you sorting on a huge part of the REFORMAT record? Or why do you have a different key for your Main Task SORT than for your JOINKEYS?
Back to top
View user's profile Send private message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Thu Oct 31, 2013 12:13 am    Post subject:
Reply with quote

Hi Bill,

I was unable to post the original data since the record length is too large

Here is an example which you can understand better

Assume key as first 3 columns in file1 i.e abc, efg etc.,

So now i am trying to match the 2 files

File 1 :

Code:
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha  789 spaces


File 2 :
Code:
???    abcalpha   01
+++  abcbetaa   01
---     abcgamma spaces


Since im giving unpaired F1 my result will be paired records as well as unpaired records in F1

Code:
abcalpha 123 01
abcalplha 123 01
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha  789 spaces


Now im trying to eliminate duplicates by sorting just upto (abcaplha 123)
so my result would be :
Code:
abcalpha 123 01
efgaplha 456 spaces
hijalpha  789 spaces

But unfortunately my result is like
Code:
abcalpha 123 spaces
efgaplha 456 spaces
hijalpha  789 spaces



Please correct me if i am trying to do some thing wrong
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Oct 31, 2013 2:07 am    Post subject: Reply to: Need help in Joins
Reply with quote

OK. I still have no clue of what you actually want. You don't want space? Is that it?

If so, put OMIT COND=(start,2,CH,EQ,C' ') in JNF2CNTL.

If you only want one record for your output, and it doesn't matter which because they both have 01, use IFTHEN=(WHEN=GROUP for your key, PUSH a sequence number, and on OUTFIL INCLUDE=(sequence number position,length,ZD,EQ,1) or similar.

SORTing just so you can use SUM FIELDS=NONE is wasteful of resources.
Back to top
View user's profile Send private message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Thu Oct 31, 2013 8:13 am    Post subject:
Reply with quote

Thanks Bill,

I will try that today and let you know if i have any questions
Back to top
View user's profile Send private message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Thu Oct 31, 2013 6:11 pm    Post subject:
Reply with quote

Hi Bill,

Today i tried with the logic provided

Card used:

Code:
JOINKEYS FILE=F1,FIELDS=(9,7,A)                   
JOINKEYS FILE=F2,FIELDS=(12,7,A)                   
JOIN UNPAIRED,F1                                   
REFORMAT FIELDS=(F1:1,569,F2:1,355),FILL=X'FF'     
SORT FIELDS=COPY                                   
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
               AND,(9,7,CH,EQ,581,7,CH)),         
        PUSH=(568:921,2)),   --> trying to group the matched records and push the last 2 columns from file 2 to file 1                     
       IFTHEN=(WHEN=GROUP,BEGIN=((1,1,BI,NE,X'FF'),
               AND,(570,1,BI,EQ,X'FF')),         
        PUSH=(1:1,569))-->when there is no match in file 2 found write the file 1 record as it is                           
OUTFIL BUILD=(1,569)


But unfortunately i was unable to get the result


Here is an simple example for better understanding. Assume the key is first 2 charcters i.e (ad, ef, and hi respectively)

File1:

Code:
abcd spaces
efgh  spaces
hijk   spaces


File2:

Code:
abza 01
abzb 01
abzc 01
abzd spaces
efza 02
efzb 02


Output expected:
Code:
abcd 01 --> for one ab record in file 1 there are 4 matches in file2
abcd 01
abcd 01
abcd spaces
efgh 02--> for one ef record in file1 there are 2 matches in file2
efgh 02
hijk spaces --> no matches hence writing record as it is


Final output should be like:
Code:
abcd 01
efgh 02
hijk spaces



My ultimate goal is to update the file 1 with the codes present in file 2 in the last 2 columns for matching key
If there is no match then write the record as it is

Main problem is occurring in following cases

1.When the matched record is having a value in last 2 columns
2.When the same matched record is also having spaces in last 2 columns

In such scenario i was getting spaces in my output file

Assume
File1 - 20 records
File2 - 30 records
Matching in both - 10 records

Now my output file count should be - 20 records (same as file1) with 10 records having the code at last taken from file 2

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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu Oct 31, 2013 6:38 pm    Post subject: Reply to: Need help in Joins
Reply with quote

I'm going to assume for now that you have SyncSort. If you do have DFSORT, post the sysout from the step so we can see your software level.

SyncSort questions lives in the JCL forum.

I have Code'd your post, again. Please learn how to use the Code tags.

It seems you have no use of the records with space from File 2. So, add OMIT=(start,2,CH,EQ,C' ') to your JOINKEYS for F2.

You (SyncSort) don't have KEYBEGIN. Your BEGIN is making a group for every record.

Use IFTHEN=(WHEN=INIT to append a sequence number with RESTART=(9,7). For now, as you only seem to have a single value or space on the F2, forget the GROUP.

For OUTFIL, use INCLUDE=( to only keep those with a sequence number of one.
Back to top
View user's profile Send private message
rajendra kalepu

New User


Joined: 29 Jul 2011
Posts: 21
Location: India

PostPosted: Wed Nov 20, 2013 5:00 pm    Post subject:
Reply with quote

Hi Bill,

Sorry for the late reply.

Your inputs helped a lot in achieving desired results. Especially the grouping and use of PUSH key word.

Thank you very much
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sort Joins umanaga DFSORT/ICETOOL 7 Fri May 25, 2012 4:56 pm
No new posts joins four types..can we use all in DB2 kumar119119 DB2 2 Mon Jun 28, 2010 11:18 pm
This topic is locked: you cannot edit posts or make replies. Difference between Corrrelated Subque... Sudeshna Sarkar DB2 2 Sat May 15, 2010 3:18 pm
No new posts For Update Of in Cursor with Joins RAJA-RAJA DB2 1 Mon Apr 12, 2010 6:05 pm
No new posts DB2 joins dick scherrer DB2 2 Wed Oct 28, 2009 6:19 am


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