With R:BASE's procedural language, you can use various code structures to accumulate variables from the data values in two different tables. Example 6 uses two DECLARE CURSOR commands to repeatedly locate the values and do the accumulations until all data has been used. Example 7 is faster. It uses one DECLARE CURSOR and one COMPUTE command to accumulate the sum. Example 8, the fastest, uses an INSERT command with a SELECT statement to accumulate the sum.
SET VAR t1 TIME = NULL
SET VAR t2 TIME = NULL
SET VAR vDiff INTEGER = NULL
--Example 6 - Slow
SET VAR t1 = (.#TIME)
SET VAR vLength = 0
DROP TABLE Totals
CREATE TABLE Totals (State TEXT 15, Length INTEGER)
DECLARE c1 CURSOR FOR SELECT State, StCode +
FROM States +
WHERE State IN ('Washington', 'Oregon', 'California')
OPEN c1
WHILE SQLCODE = 0 THEN
FETCH c1 INTO vState INDICATOR ivState, +
vStCode INDICATOR ivStCode
IF SQLCODE <> 0 THEN
BREAK
ENDIF
DECLARE c2 CURSOR FOR SELECT Runway +
FROM Airports WHERE StCode = .vStCode
OPEN c2
WHILE SQLCODE = 0 THEN
FETCH c2 INTO vRunway INDICATOR ivRunway
IF SQLCODE = 0 THEN
SET VAR vLength = (.vLength + .vRunway)
ENDIF
ENDWHILE
INSERT INTO Totals VALUES (.vState, .vLength)
DROP CURSOR c2
ENDWHILE
DROP CURSOR c1
SET VAR t2 = (.#TIME)
SET VAR vDiff = (.t2 - .t1)
SET VAR vTime = (RTIME(0,0,.vDiff))
PAUSE 2 USING .vTime
--Example 7 - Faster
SET VAR t1 = (.#TIME)
DROP TABLE Totals
CREATE TABLE Totals (State TEXT 15, Length INTEGER)
DECLARE c1 CURSOR FOR SELECT State, StCode FROM States +
WHERE State IN ('Washington', 'Oregon', 'California')
OPEN c1
WHILE SQLCODE = 0 THEN
FETCH c1 INTO vState INDICATOR ivState, +
vStCode INDICATOR ivStCode
IF SQLCODE <> 0 THEN
BREAK
ENDIF
COMPUTE vLength AS SUM Runway FROM Airports +
WHERE StCode = .vStCode
INSERT INTO Totals VALUES (.vState, .vLength)
ENDWHILE
DROP CURSOR c1
SET VAR t2 = (.#TIME)
SET VAR vDiff = (.t2 - .t1)
SET VAR vTime = (RTIME(0,0,.vDiff))
PAUSE 2 USING .vTime
--Example 8 - Fastest
SET VAR t1 = (.#TIME)
DROP TABLE Totals
CREATE TABLE Totals (State TEXT 15, Length INTEGER)
INSERT INTO Totals SELECT State, SUM(Runway) +
FROM States,Airports +
WHERE States.StCode = Airports.StCode +
AND State IN ('Washington', 'Oregon', 'California') +
GROUP BY State
SET VAR t2 = (.#TIME)
SET VAR vDiff = (.t2 - .t1)
SET VAR vTime = (RTIME(0,0,.vDiff))
PAUSE 2 USING .vTime
