phoenix_me
Member
I have a table like below:
with the following data inside:
Now I would like to create BREAK BY Amount, CustID, Country, City and AcctNum but I have some conditions:
1. AcctNum - take under consideration only 3 first digits [so 67241 and 67256 will be in same 'BREAK BY' section]
2. Amount - 'BREAK BY' for Amount LT 0 and Amount GT 0 [so -120, -15 -80 will be in one 'BREAK BY' and 115, 74 will in antoher]
It should be 'BREAKED BY' into 'groups' like in BREAK BY column
So the output should give me 3 lines:
How can I achieve this in 4GL FOR EACH statement?
Code:
+-----------+
| Payments |
+-----------+
|Amount |
|CustID |
|Country |
|City |
|AcctNum |
+-----------+
Code:
Amount CustID Country City AcctNum
-120 001 GER MUN 67241
-120 001 GER MUN 67256
-15 001 RUS MOS 67123
-80 001 RUS MOS 67134
75 001 RUS MOS 67134
1. AcctNum - take under consideration only 3 first digits [so 67241 and 67256 will be in same 'BREAK BY' section]
2. Amount - 'BREAK BY' for Amount LT 0 and Amount GT 0 [so -120, -15 -80 will be in one 'BREAK BY' and 115, 74 will in antoher]
It should be 'BREAKED BY' into 'groups' like in BREAK BY column
Code:
Amount CustID Country City AcctNum | BREAK BY |
-120 001 GER MUN 67241 | 1 |
-120 001 GER MUN 67256 | 1 |
-15 001 RUS MOS 67123 | 2 |
-80 001 RUS MOS 67134 | 2 |
75 001 RUS MOS 67134 | 3 |
Code:
Number CustID Country City AcctNum Amount
1 001 GER MUN 672 -240 [-120 + -120]
2 001 RUS MOS 671 -95 [-15 + -80 ]
3 001 RUS MOS 671 75 [75]