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 handle duplicate records in ezytrieve..
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CA Products
View previous topic :: :: View next topic  
Author Message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sat Apr 28, 2012 9:23 am    Post subject: How to handle duplicate records in ezytrieve..
Reply with quote

Hi,

I am using 2 different input files in my ezytreive job where I have used Customer_number as matching field. But in my job 1st input file will not have duplicates but 2nd input files is having duplicates. As per my knowledge easytrieve will not process duplicate records in the 2nd file and same is happening for me too. But i want to process duplicate records in the 2nd file and write to report based on my condition.

I have searched all over net but didn't find any solution for processing duplicate record. I did come across the condition IF DUPLCIATES but not sure how it will help me out.

Can anyone please guide me to resolve this. Even I searching for manuals from long.

Thanks,
Chetan
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: Sat Apr 28, 2012 9:43 am    Post subject:
Reply with quote

Hello and welcome to the forum,

Consider using your file2 as file1 and your file1 as file2. . .
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sat Apr 28, 2012 10:25 am    Post subject:
Reply with quote

Dick,

I have tried using file2 as file1 and file1 as file2 but still I am not getting expected results. Duplicates in the 1st file are also not getting processed.

Thanks,
Chetan
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Apr 28, 2012 1:03 pm    Post subject: Reply to: How to handle duplicate records in ezytrieve..
Reply with quote

If you have duplicates on both files, the "synchronised file processing" gets tricky.

If you can't get rid of duplicates on one of the files, then I'd recommend your own two-file match (there is a "sticky" in the Cobol forum with logic) or "sideways match".

Perhaps if you can explain how your processing should operate, with respect to the presence/absence of keys and duplicates on either file.

Also, knock yourself up a couple of "test files" (they can be DD *) so you can understand what the synchronised processing is doing to your sample data. Keep it very simple.
Code:

A 01 F1
A 02 F1
A 03 F1
B 01 F1
D 01 F1
D 02 F1

A 01 F2
C 01 F2
D 01 F2
D 01 F2


The DISPLAY what you get, reverse the files (changing the F1 and F2) and see again.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Sat Apr 28, 2012 2:44 pm    Post subject:
Reply with quote

I used something like this :
Code:

JOB INPUT(XREF1 KEY(PSB) XREFM KEY(PSBM))
IF MATCHED
*  DISPLAY 'MERGE ' TRANS ' ' PSBM ' ' DBDM
   TRANS2 = TRANS
   PSB2 = PSBM
   DBD2 = DBDM
   PROCESS2 = PROCESSM
   PUT XREF2
   HDBD = DBDM
   HPROCESS = PROCESSM
END-IF
IF DUPLICATE XREF1 AND NOT FIRST-DUP XREF1
*  DISPLAY 'XREF1 ' TRANS ' ' PSB  ' ' HDBD
   TRANS2 = TRANS
   PSB2 = PSB
   DBD2 = HDBD
   PROCESS2 = HPROCESS
   PUT XREF2
END-IF


Maybe you can use that logic to get a solution for your problem.
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sat Apr 28, 2012 2:50 pm    Post subject:
Reply with quote

Bill,

As I said earlier I have 2 files with below details in it:
File1 contains Customer details (like name, adress, SSN etc)
File2 contains Customer transaction details (like purchases, payments etc)
Customer number is the matching field in both the files. File1 will not contain duplicates but File2 is having dupilcates. I want to process all the duplicate records in the File2 for corresponding record in File1.

My Ezytrieve job not at all processing duplicate record whether you use duplicate file as 1st file or 2nd file. Either way it is not processing duplicate records.

This is how my files looks like...

File1:
Cust Name City
------ ------- --------
0011 Chetan Mysore
0012 Bill Portugal
etc...

File2:
Cust Item Amount($)
----- ------ ------------
0011 Pant 100.00
0011 Shirt 150.00
0012 Tshirt 300.00
0012 Bag 350.00
0012 Shoes 300.00
etc..

Thanks,
Chetan
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Apr 28, 2012 3:01 pm    Post subject:
Reply with quote

chetanambi wrote:
Dick,

I have tried using file2 as file1 and file1 as file2 but still I am not getting expected results. Duplicates in the 1st file are also not getting processed.

Thanks,
Chetan


Chetan, I picked up your thread from here.

Since Easytrieve does handle duplicates on the 2nd file in an useful manner, I assumed you had duplicates on both.

So, now to look back at your original.

Any chance of seeing the code you have for the matching? That's likely where the problem lies.

EDIT: Looking at your first post, your "knowledge" that Easytrieve can't process duplicates on the second file is just wrong. Where did you get that idea?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Apr 28, 2012 4:14 pm    Post subject:
Reply with quote

Code:
JOB INPUT( +
           FILE1 KEY ( FILE1-KEY ) +
           FILE2 KEY ( FILE2-KEY ) +
         )
IF MATCHED
    DISPLAY "MATCHED FILE1-KEY >" FILE1-KEY "< FILE2-KEY >" FILE2-KEY +
             "< FILE2-DATA ">" FILE2-DATA "<"
    GO TO JOB
END-IF


With you input should show
Code:

MATCHED FILE1-KEY >0011< FILE2-KEY >0011<><Pant 100.00  <
MATCHED FILE1-KEY >0011< FILE2-KEY >0011<><Shirt 150.00 <
MATCHED FILE1-KEY >0012< FILE2-KEY >0011<><Tshirt 300.00<
MATCHED FILE1-KEY >0012< FILE2-KEY >0011<><Bag 350.00   <
MATCHED FILE1-KEY >0012< FILE2-KEY >0011<><Shoes 300.00 <


I can't test this, because I don't have access to Easytrieve.

I always put the file with duplicates as the 2nd file. With duplicates on both files, code the match myself.

Peter's code is for a specific situation where duplicates on the first file have a guaranteed match to the second file, otherwise data will be "stolen" from the previous match (if there was one, else space or zero, depending on definition of the "H" fields). Unless I'm missing something, which is always possible :-)

Also, please use the Code tags to preserve spacing, it makes postings much easier to read.
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Sat Apr 28, 2012 9:52 pm    Post subject:
Reply with quote

Bill,

its just an example to process duplicates, in this case for the first file. It shouldnt be that difficult to do the same for the other file.
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: Sat Apr 28, 2012 10:41 pm    Post subject:
Reply with quote

Hello,

Understanding that the "knowledge" was incorrect, i suggested "flipping" the files.

Easytrieve (and the 2-file match/merge from the sticky) need special care when processing a many-to-many situation. If the data has only duplicates in one file, it should not be a problem.

If you post the code related to the "match", we may be able to offer a suggestion.
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sat Apr 28, 2012 11:05 pm    Post subject:
Reply with quote

Hi all,

Out of 2 files only one file is having duplicates. I have tried using duplicate file as 1st file and 2nd file but still not getting correct results.

a) with duplicate file as 1st file (SCAFILE):
Code:

JOB INPUT (SCAFILE KEY (SCA-CUST-NO) +                     
           SCCFILE KEY (SCC-CUST-NO)) +                   
           FINISH EOJ-RTN                                 
                                                           
       IF MATCHED                                         
          IF SCA-PAYOUT-VSN EQ 14                         
             IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68
               
                ----populate values for match file---
               
                PUT MATCHFL                               
             END-IF                                       
          END-IF                                           
       END-IF                                             
                                                           
       IF DUPLICATE SCAFILE AND NOT FIRST-DUP SCAFILE     
          IF SCA-PAYOUT-VSN EQ 14                         
             IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68
               
                ----populate values for match file---     
                     
                PUT MATCHFL                               
             END-IF                                       
          END-IF                                           
       END-IF                                             
                                                           
       GO TO JOB.                                         
                                                           
 EOJ-RTN.  PROC                                           
    DISPLAY 'FINISHED PROCESSING!!!!'                     
 END-PROC.                                                 


b) with duplicate file as 2nd file:
Code:

JOB INPUT (SCCFILE KEY (SCC-CUST-NO) +                   
           SCAFILE KEY (SCA-CUST-NO)) +                   
           FINISH EOJ-RTN                                 
                                                         
       IF NOT MATCHED                                     
          GO TO JOB                                       
       END-IF.                                           
                                                         
       IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68     
          IF SCA-PAYOUT-VSN EQ 14                         
                     
             ---- populate values for matching report file--
             
             PUT MATCHFL                                 
          END-IF                                         
       END-IF                                             
                                                         
       GO TO JOB.                                         

EOJ-RTN.  PROC                           
   DISPLAY 'FINISHED PROCESSING!!!!'     
END-PROC.                               


Please guide me what I am missing here..


Thanks,
Chetan
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Apr 28, 2012 11:29 pm    Post subject: Reply to: How to handle duplicate records in ezytrieve..
Reply with quote

Have you got some sample data to go with it please? Relevant filelds. Input files, output you are getting, output you expect,
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sun Apr 29, 2012 1:07 pm    Post subject:
Reply with quote

Bill,

Your are right!!..

I have used file with duplicates as 2nd file in my job and now i am getting correct results. I have used below code:

Code:

JOB INPUT (SCCFILE KEY (SCC-CUST-NO) +                         
           SCAFILE KEY (SCA-CUST-NO)) +                       
           FINISH EOJ-RTN                                     
                                                               
       IF MATCHED                                             
          IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68       
             IF SCA-PAYOUT-VSN EQ 14                           
                               
                //// populate data for report///
               
                PUT MATCHFL                                   
             END-IF                                           
          END-IF                                               
       END-IF                                                 
                                                               
       GO TO JOB.                                             
                                                               
 EOJ-RTN.  PROC                                               
    DISPLAY 'FINISHED PROCESSING!!!!'                         
 END-PROC.                                                     


Thank you all for your help!!.

Thanks,
Chetan
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Apr 29, 2012 2:42 pm    Post subject: Reply to: How to handle duplicate records in ezytrieve..
Reply with quote

Well, I'm glad it is working if you are sure it is :-)

Don't forget that Dick pointed you this way, twice, as well and that Peter contributed an outline for duplicates on the first file.

I don't see how the matching code you are showing now is logically different from what you were showing for duplicates on file 2 before. I also can't see how the logic you had for matching the duplicates on the first file, along the same lines that Peter showed, would not work with the small sample of data that you showed.

I was thinking it had to be an error in setting up the output values which made you think the matching didn't work because of your previous "knowledge" :-)
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Sun Apr 29, 2012 5:14 pm    Post subject:
Reply with quote

Thanks to Dick, Peter and to everyone!!..

Bill,

The main difference in logic from the code which i showed before and from the which worked out correctly is i didnt use IF MATCHED before. You can have a look at my before logic and logic which worked. You ll get the difference.

After trying all possible ways n searching over internet I thought Ezy wont handle duplicates. Hence I came to my false "knowledge" icon_smile.gif icon_smile.gif

Thanks,
Chetna
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Apr 29, 2012 5:41 pm    Post subject: Reply to: How to handle duplicate records in ezytrieve..
Reply with quote

You used IF MATCHED before, you just negated it to exclude unmatched from getting any further.

Code:
       IF NOT MATCHED                                     
          GO TO JOB                                       
       END-IF.                                           
* Here you only get matched records                                                         
       IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68     
          IF SCA-PAYOUT-VSN EQ 14                         
             PUT MATCHFL                                 
          END-IF                                         
       END-IF                             


In the second version, only the MATCHED go further inside the nested IF.

Code:
        IF MATCHED                   
* Here you only get matched records                         
          IF SCC-CURR-VER EQ 68 AND SCC-DSCLSD-VER EQ 68       
             IF SCA-PAYOUT-VSN EQ 14                           
                PUT MATCHFL                                   
             END-IF                                           
          END-IF                                               
       END-IF                                                 


The effect is the same, whichever of these two you happen to code. This is nothing to do with Easytrieve, it is just the logic of "code". I don't know how one would work over the other.

The only thing I can see that is "out of the ordinary", is the period after the END-IF. I've never done that, but don't see that it could affect anything, as the effect of the period is just to allow multiple statements/language constructs on the same line, it is not a scope-delimeter or anything to affect the flow of logic.

Your GO TO JOB with a period you just don't need, because Easytrieve does that automatically at that point (before first PROC or next JOB/SORT).

If you genuinely have changed nothing but the code above (ie the detail of setting up the matched record is the same) then I can't see one working and the other not, so it becomes "interesting" :-)
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 Apr 30, 2012 12:48 am    Post subject:
Reply with quote

Hello,

Good to hear it is working - thank you for letting us know icon_smile.gif

Suggest really thorough testing to make sure it works for all cases. It sounds like there may still be "opportunitites".

Quote:
searching over internet I thought Ezy wont handle duplicates. Hence I came to my false "knowledge"
One of the problems with things posted on the internet is that many of the info is incorrect - and if one does not alreadh know, some of the incorrect info looks better than some of the correct info.

Using info from the vendor (in this case CA) is better than taking someone's thoughts that are not based n fact.
Back to top
View user's profile Send private message
Jose Mateo

Active User


Joined: 29 Oct 2010
Posts: 110
Location: Puerto Rico

PostPosted: Mon Apr 30, 2012 7:46 pm    Post subject:
Reply with quote

Good day, Mr. scherrer!

In Easytrieve, if you are doing synchronized file processing you could bypass duplicate records by testing the conditions DUPLICATE, FIRST-DUP or LAST-DUP on the same file by key but Easytrieve does process duplicate records if the coding is not bypassing those records.
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 Apr 30, 2012 9:12 pm    Post subject: Reply to: How to handle duplicate records in ezytrieve..
Reply with quote

Hi Jose,

Yup, hopefully chetanambi now understands this icon_wink.gif

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

New User


Joined: 21 Jan 2012
Posts: 53
Location: India

PostPosted: Tue May 01, 2012 12:31 pm    Post subject:
Reply with quote

Hello everyone,

Now I really understand the how duplicates will be processed in Ezytrieve. My "knwoledge" was wrong indeed icon_confused.gif icon_confused.gif .

Duplicates will be processed unless we skip it using DUPLICATES, FIRST-DUP and LAST-DUP as Jose mentioned. But need to use MATCHED and NOT MATCHED in correct place. Because wrong use of MATCHED was giving incorrect results for me.

Thank you all..

Regards,
Chetan
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 -> CA Products All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

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 split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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