OS2 World Community Forum
OS/2, eCS & ArcaOS - Technical => Programming => Topic started by: Per E. Johannessen on May 14, 2023, 05:26:48 pm
-
In a VX-Rexx program I have this container, QRY_2:
|TEKSTFELT | TALLFLT1 | TALLFLT2 |
|aaa | 10.00| |
|aaa | 10.00| |
|aaa | 10.00| |
|bbb | 100.00| |
|bbb | 100.00| |
|bbb | 100.00| |
|ccc | 1000.00| |
|ccc | 1000.00| |
|ccc | 1000.00| |
Running this code (from a push button):
ok = VRMethod( "QRY_2", "MoveFirst" )
do while( ok = 1 )
call VRMethod "QRY_2", "RowData", "data."
str = ""
do i = 1 to data.0
str = str data.i
end
say str
ok = VRMethod( "QRY_2", "MoveNext" )
end
Results in:
aaa 10.00 DATA.3
aaa 10.00 DATA.3
aaa 10.00 DATA.3
bbb 100.00 DATA.3
bbb 100.00 DATA.3
bbb 100.00 DATA.3
ccc 1000.00 DATA.3
ccc 1000.00 DATA.3
ccc 1000.00 DATA.3
The problem is that I need the third column in the container, TALLFLT2 (displayed as DATA.3 above), to get updated with cumulative values from column TALLFLT1, also it must «reset» when the value in TEKSTFELT changes, like this:
|TEKSTFELT | TALLFLT1 | TALLFLT2|
|aaa | 10.00| 10.00|
|aaa | 10.00| 20.00|
|aaa | 10.00| 30.00|
|bbb | 100.00| 100.00|
|bbb | 100.00| 200.00|
|bbb | 100.00| 300.00|
|ccc | 1000.00| 1000.00|
|ccc | 1000.00| 2000.00|
|ccc | 1000.00| 3000.00|
Ideas are welcome.
-
In a VX-Rexx program I have this container, QRY_2:
The problem is that I need the third column in the container, TALLFLT2 (displayed as DATA.3 above), to get updated with cumulative values from column TALLFLT1, also it must «reset» when the value in TEKSTFELT changes, like this:
|TEKSTFELT | TALLFLT1 | TALLFLT2|
|aaa | 10.00| 10.00|
|aaa | 10.00| 20.00|
|aaa | 10.00| 30.00|
|bbb | 100.00| 100.00|
|bbb | 100.00| 200.00|
|bbb | 100.00| 300.00|
|ccc | 1000.00| 1000.00|
|ccc | 1000.00| 2000.00|
|ccc | 1000.00| 3000.00|
Ideas are welcome.
Apart from REXX and VX-Rexx, of which I remember too little, the idea is keeping track of all the equal elements in DATA.1 and increment the cells in DATA.3 by the amount of what is present on the corresponding cell in DATA.2 (convoluted, I know: I'm terribly sleepy today).
One idea would be to follow a SQL-like approach, selecting all distinct elements in DATA.1 and for each cell in DATA.3 sum the actual amount of DATA.2.
Seeing as the rows in DATA.1 are already grouped by equal elements that would be not so difficult.
Another idea, more trivial but clever:
1) keep a counter of the current cell
2) save the current value of DATA.1 in an object, and the current value of DATA.3 in another (current cells)
3) cycle on all elements of DATA.1:
3.1) while DATA.1 is equal to the saved value, add to the current cell of DATA.3 the previous value + DATA.2
3.2) if DATA.1 is different, repeat the cycle saving the new value and starting from empty values of DATA.2 and DATA.3
Clear as fog, I know...
Mentore
-
Thanks Mentore, I have already tried with SQL and I think that's the best way. I've been trying various SQL statements that I have found on the net with DBExpert but so far no success. DBE does not come with any documentation of valid SQL-statements and I haven't found any online manual either. However, even if I could make it work with DBE I probably will have trouble making it work with VX-Rexx.
-
Try something like:
ok = VRMethod( "QRY_2", "MoveFirst" )
row = 0
do while( ok = 1 )
call VRMethod "QRY_2", "RowData", "data."
prev = row
row = row + 1
if row == 1 then
data.row.3 = data.row.2
else
data.row.3 = data.prev.3 + data.row.2
say data.row.1 ',' data.row.2 ',' data.row.3
ok = VRMethod( "QRY_2", "MoveNext" )
end
-
Assuming strict ordering of field TEKSTFELT:
ok = VRMethod( "QRY_2", "MoveFirst" )
identifier = ''
sum = 0
do while( ok = 1 )
call VRMethod "QRY_2", "RowData", "data."
if data.1 <> identifier then do
identifier = data.1
sum = data.2
end
else do
sum = sum + data.2
end
data.3 = sum
say data.1 ',' data.2 ',' data.3
ok = VRMethod( "QRY_2", "MoveNext" )
end
-
Thank you both.
Alex, I got this when running your code:
*** Error 41 Bad aritmetic conversion
Lars, works fine.
Initially I had the idea that the container should display these cumulative values but there is actually no point in that.
Will use the code to create some accounting reports which also can be printed to screen.
-
Tried to create a report, which works but it is missing the cumulative values.
I added the following to Lars' code:
reprows = 0
ok = VRMethod( "QRY_2", "MoveFirst" )
do while( ok = 1 )
call VRMethod "QRY_2", "RowData", "data."
reprows=reprows+1
table.reprows.TEKSTFELT = data.1
table.reprows.TALLFLT1 = data.2
table.reprows.TALLFLT2 = data.3
ok = VRMethod( "QRY_2", "MoveNext" )
end
table.0=reprows
DO i=1 TO table.0
END i
hwnd = VRGet( "Window1", "HWnd" )
rc=VpPrintReport(hwnd, 'REP_HBAKKM.vpt', 'table.', '<PREVIEW>' )
call QRY_2_Fini
return
Attached is the resulting report which would be fine if the column now showing data.3 would show the cumulative instead.
-
Well, there is only data from "QRY_2" that is being used in the report.
Writing to a txt-file is OK, like this;
outfile = data.1 ',' data.2 ',' data.3
out = lineout(testakkm.txt,outfile)
So, how to define the same data as source for the VPR-report REP_HBAKKM.vpt ?
-
In case someone is interested, this code works:
reprows = 0
ok = VRMethod( "QRY_2", "MoveFirst" )
identifier = ''
sum = 0
do while( ok = 1 )
call VRMethod "QRY_2", "RowData", "data."
if data.1 <> identifier then do
identifier = data.1
sum = data.2
end
else do
sum = sum + data.2
end
data.3 = sum
reprows=reprows+1
data.reprows.TEKSTFLT = data.1
data.reprows.TALLFLT1 = data.2
data.reprows.AKKUM = data.3
ok = VRMethod( "QRY_2", "MoveNext" )
end
data.0=reprows
DO i=1 TO data.0
END i
hwnd = VRGet( "Window1", "HWnd" )
rc=VpPrintReport(hwnd, 'REP_HBAKKM.vpt', 'data.', '<PREVIEW>' )