View previous topic :: View next topic
|
Author |
Message |
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Consider using your file2 as file1 and your file1 as file2. . . |
|
Back to top |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
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 |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009 Posts: 2481 Location: Netherlands, Amstelveen
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
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"
Thanks,
Chetna |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Good to hear it is working - thank you for letting us know
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 |
|
|
Jose Mateo
Active User
Joined: 29 Oct 2010 Posts: 121 Location: Puerto Rico
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Jose,
Yup, hopefully chetanambi now understands this
d |
|
Back to top |
|
|
chetanambi
New User
Joined: 21 Jan 2012 Posts: 58 Location: India
|
|
|
|
Hello everyone,
Now I really understand the how duplicates will be processed in Ezytrieve. My "knwoledge" was wrong indeed .
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 |
|
|
lonerusher
New User
Joined: 30 Nov 2010 Posts: 4 Location: India
|
|
|
|
1. Driver File primary key(means first file primary in Job Input) should always have unique records if you are using key in Job input. If your driver file do not have unique records you need to declare the Job Input as Null and need to handle the file balancing logic using the DO WHILE logic.
2. Secondary File in Job Input : In secondary file its optional that you will always have unique records means it can have duplicate records for the primary key. Now if you want those duplicate elements in your output file just simply use job input and use if matched logic.
If you don't want duplicates into your output file, then you need to handle that using Job input NULL and do while. Alternatively you can do that using flag logic like storing the primary key into some temp variable and always comparing the new value and last value and then putting your logic.
3. If you are not sure about your input files always use JOB INPUT file as NULL and handle the files using DO while logic.
Enjoy ! hope this helps you in future.. |
|
Back to top |
|
|
|