I have written the stored procedure below, but seem to be having problems
with the comarison AP_MAC=:cur_ap_mac (the XXX line). I've even tried
casting it as: AP_MAC=cast(:cur_ap_mac as char(12)). It just outputs <null>
for ap_x_coord and ap_y_coord. If I explicitly specify ap_mac:
AP_MAC='000949249134', then I do get values for ap_x_coord and ap_y_coord
(so the ap_locations table lookup does not seem to be the problem here
either).


SET TERM ^^ ;
CREATE PROCEDURE MINUTEBYMINUTE2
(
BEGINTIME TimeStamp,
ENDTIME TimeStam
)
returns
(
USERS Char(12),
CUR_AP_MAC Char(12),
CUR_AP_STRENGTH SmallInt,
MINUTE1 TimeStamp,
AP_X_COORD Integer,
AP_Y_COORD Integer
)
AS

DECLARE VARIABLE minute2 TIMESTAMP;

BEGIN

minute1 = begintime;
minute2 = minute1 + .00069444444444444444;
WHILE (minute1 < endtime)
DO
BEGIN

FOR SELECT ID, AP_MAC, AP_STRENGTH FROM WTD_DATA where
cor_sam_time between :minute1 and :minute2 GROUP BY ID, AP_MAC, AP_STRENGTH
INTO :users, :cur_ap_mac, :cur_ap_strength
DO
BEGIN
SELECT X_COORDINATE, Y_COORDINATE FROM AP_LOCATIONS where
AP_MAC=:cur_ap_mac INTO :ap_x_coord, :ap_y_coord; /* XXX */
SUSPEND;
END

minute1 = minute2;
minute2 = minute2 + .00069444444444444444;
END

END
^^
SET TERM ; ^^



The declarations for the two tables (WTD_DATA and AP_LOCATIONS) are:

CREATE TABLE WTD_DATA
(
ID CHAR( 12) CHARACTER SET NONE
COLLATE NONE,
IN_TIME TIMESTAMP,
DEV_UPLDTIME TIMESTAMP,
SAM_TIME TIMESTAMP,
AP_MAC CHAR( 12) CHARACTER SET NONE COLLATE
NONE,
AP_STRENGTH SMALLINT,
WTD_VERSION NUMERIC( 18, 0),
PROD_INFO_FLAG SMALLINT,
VALID SMALLINT DEFAULT 1 NOT NULL,
COR_SAM_TIME TIMESTAMP
);

CREATE TABLE AP_LOCATIONS
(
AP_MAC CHAR( 12) CHARACTER SET NONE NOT
NULL COLLATE NONE,
X_COORDINATE INTEGER,
Y_COORDINATE INTEGER
);