MVSFORUMS.com

Telbont · Beginner Joined: 28 Apr 2008 Posts: 4 Topics: 1

Is it possible using SORT utility (have SYNCSORT) to sum same key field records based on sign of the sum field i.e. to sum positive and negative records separately for a given key? The sum field is a S9(11)V99 zoned decimal field.

Sqlcode · Intermediate Joined: 15 Dec 2006 Posts: 157 Topics: 38

Telbont,

Could you please post some test records and expected output?

Thanks,

edkir98 · Posted: Wed Oct 01, 2008 8:18 am Post subject:

Try this

kolusu · Posted: Wed Oct 01, 2008 11:13 am Post subject:

edkir98,

A field defined as S9(11)V99 zoned decimal field will have the sign overpunched on the last byte.

Telbont · Beginner Joined: 28 Apr 2008 Posts: 4 Topics: 1

The output shown by edkir98 is exactly what I want except that the numeric field is defined as shown by kolusu so the sign is embedded on the last byte. Maybe I should try to include a bit field in the sort key such that hex C and hex D would separate out the positive and negative records.....

Dibakar · Posted: Thu Oct 09, 2008 7:37 pm Post subject:

Assuming key is from columns 1 to 5 and data from 6 to 18 you can modify the sort in http://www.mvsforums.com/helpboards/viewtopic.php?t=2593&highlight=sort+cond as -

kolusu · Posted: Thu Oct 09, 2008 8:02 pm Post subject:

Dibakar,

Did you really test the solution you posted? OUTFIL is processed after your sort and sum operations. When the outfil is processed the postive and negative values are already summed as one single record for each key.

for ex: if you have this input

Dibakar · Posted: Sat Oct 11, 2008 9:39 am Post subject:

kolusu,

I realised the mistake but before I could correct you had pointed out the mistake. No I didn't test the code, don't have access to mainframe anymore. Should have given a disclaimer. I will try to correct it.

Dibakar.

Dibakar · Posted: Sat Oct 11, 2008 10:54 am Post subject:

Telbont,

You can add two sort steps as follows -

Telbont · Beginner Joined: 28 Apr 2008 Posts: 4 Topics: 1

Thanks all for the responses and time spent. I used 3 steps as suggested by Dibakar and achieved the end result - 2 SORT INCLUDES and 1 MERGE step.

kolusu · Posted: Thu Oct 16, 2008 10:25 am Post subject:

Telbont,

Since you asked specifically for syncsort solution I haven't posted it, however I just feel it is an overkill as to what you are doing. Here is a 1 step solution which would give you the desired results

Assuming that your input is FB 80 bytes lrecl , just add an indicator at the end if the value is less than zero. Now sort on the key and the indicator at the end and sum the values. After summation just remove the additional indicator using OUTREC

Telbont · Beginner Joined: 28 Apr 2008 Posts: 4 Topics: 1

Thanks for the one step approach which works fine and is certainly more efficient than a 3 steps sort merge. Never knew much about the IFTHEN conditional keyword so a lot learnt on this topic.