MVSFORUMS.com

[shuko]

I want to compare 2 files and write the result to a third file.
Input and output files have LRECL=5000 and FB,
I have 3 keys and have the following positions in my file
ADFLAG (pos 231,32),ADJID (957,32) STATUS (1947,32)
The keys can be maximum 32 bytes

The output should be based on the following criteria

1) If keys are the same in both files (duplicate do nothing)
2) If keys are different,then two rows to be written to file3.
Record from file 1, with the amount reversed in file 3 (I could not get
this to work with MUL,-1) and also the record from file 2
3) If record from file2 not in file1 write to file3

[kolusu]

shuko,

Run the following Job and post the output of sysout here, which will give us an idea about the type of sort product we are dealing.

[shuko]

My SYSOUT

[Frank Yaeger]

Shuko,

I can see from your DFSORT messages that you have the Dec, 2004 DFSORT PTF, so you can use IFTHEN, OVERLAY and UFF. The DFSORT job below will do what I think you're asking for. I assumed that:
- you actually want the output sorted by your three keys (the output you show isn't sorted that way)
- your amount field is in positions 1-8
- your amount values can be right aligned for output
- you want the amount set negative for the records in file1 without a match in file2, and the amount left positive for the records in file2 without a match in file1 (your rules are a little ambiguous on this point).

Here's the DFSORT/ICETOOL job:

[shuko]

Frank

Your assumptions are right. Something regarding amount, if there is no leading sign for amount, then it is assumed to be positive else it is (-) for negative. So for records in file1 without a match in file2, the Amount in file1 should be reversed, which means if there is no leading sign then the Amount is assumed to be positive so the Amount from file1 should have a leading sign (-) when it appears in out file, and if the leading sign is (-), in file1,the amount should have no leading sign. (In COBOL I would do Amount * -1, to reverse the Amount).

I tried the DFSORT/ICETOOL job and my observations were
- Duplicates appear in out file ( in my case should'nt happen)
- Records in file1 without a match in file2 and with the amount reversed
does not appear in out file


Amount ADFLAG ADJID STATUS
------- ------ -------- --------

900.00 NNNNNN 1234567 BOOKED
-900.00 NNNNNN 1234567 OPEN <--- (not in out file)
1000.00 NNNNNN 7890123 OPEN

[Frank Yaeger]

Shuko,

You didn't show any negative values in the input file, so I assumed you didn't have any. If you do, then just change UFF to SFF in the two places it appears in my job. SFF handles positive or negative values whereas UFF only handles positive values. SFF,MUL,-1 will reverse the sign.

I don't understand your "observations". I ran the job I showed with the input you showed and it gave the output I showed:

[shuko]

Sorry Frank, my mistake. I used my Test JCL and your control statements and this
resulted in the wrong output. I ran your Job and it worked like a charm for me.
Thank you. You mentioned something interesting about printing input fields.
Can you tell me how to do this. This could be really handy, during tests.

[Frank Yaeger]

[shuko]

With the above Job from Frank, the record from file1 with the amount reversed and the record from file2 unchanged, is written to file3 perfectly.
Now I have an additional requirement, when the status in file2 is SETTLED and the STATUS in file1 is OPEN or BOOKED. In this case
I want the record from file1 to be written to file3 only if the difference in Amount not equals 0.

AMOUNT ADFLAG ADJID STATUS
---------------------------------------------------------
Case1
950 N 1000000001 BOOKED (file1 )
950 N 1000000001 SETTLED (file2 )

Case2
950 N 1000000001 BOOKED (file1 )
850 N 1000000001 SETTLED (file2 )

Expected Case1 result

Since the amount from file1 gets reversed ( -950) and file 2 has +950
resulting in 0, only the record from file2 should be in file3.

AMOUNT ADFLAG ADJID STATUS
-------------------------------------------------------
950 N 1000000001 SETTLED

Expected Case2 result

-950 N 1000000001 BOOKED
850 N 1000000001 SETTLED
850 N 1000000001 BOOKED

The logic is, for every SETTLED found in File2 generate another record with the status BOOKED with the same amount as in file2,check
if the reversed amount from file1 and the generated BOOKED amount results in 0
if YES write only the record from File2 (SETTLED) to file3
if NO, then 3 records in file3 as shown in red.
I tried to get my new requriement to work, but did not have much luck.
Any help will be greatly appreciated.

[Frank Yaeger]

Is this a completely separate requirement, or an addition to the other requirement? Please show an example of the input records and expected output that illustrates ALL of the rules you want to use.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

[shuko]

This is an addition to the previous requirement. The files length and keys are the same (3 keys and with the following positions in my file
ADFLAG (pos 231,32),ADJID (957,32) STATUS (1947,32). The previous requirement was file1 with a status of OPEN and file 2 with a status of BOOKED, the OUT file had record from file1 with Amount reversed and the record from file2 and also any record from file2 not in file1. Now file2 can have a status of SETTLED which is shown in Case1 where the amount from file1 and file2 gives 0, and Case2 where 3 records are generated from 2 since the Amount not = 0. Since this is an additional requirement, I did not start a new topic. Have I given all the necessary information?

[Frank Yaeger]

[shuko]

Here are all the rules

File1

[Frank Yaeger]

I just had an idea for a much less complex way to do this kind of thing. I'll see if I can work it out sometime tomorrow. Stay tuned.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

[shuko]

Sorry Frank for underestimating the complexity of my requirement.
I was trying to integrate my additional requirement, with the posted job
without losing this functionality. Additionaly I just want to check the
status of file2 and if it is SETTLED, check the AMOUNT in File1 (taking into
consideration it is reversed) and FIle2, if this gives 0,discard the row from
file1 and if it is not = 0, then record from file1 with reversed Amount in
file3,record from file2 in file3, the posted job does this and now addtionally
I need the record from file2 again in file 3 the same AMOUNT but only
the Status as BOOKED.
I was trying to figure out a way to eliminate the file1 record from OUT file,if amount = 0 and add an addtional record to out file if amount not=0 by checking the STATUS in file2 for SETTLED ,to the existing functional job. I thought this would only need some tweaking to the existing Job.