Hi:

This works fine but takes just as long (13 seconds) as running
as a SELECT.

Any suggestions for speeding this up appreciated.

Best
regards

SET TERM ## ;
CREATE PROCEDURE PROPERTYOWNER (Window INTEGER)
RETURNS(ID INTEGER,
MGRID INTEGER,
ADRESS_TEN CHAR(7),
STNAME_TEN VARCHAR(25),
CITY_TEN VARCHAR(16),
STATE_TEN VARCHAR(2),
ZIP_TEN CHAR(5),
MAXDATE DATE)
AS
BEGIN
FOR
SELECT DISTINCT ID, MGRID, CAST(CAST(ADRESS_TEN AS INTEGER) AS
CHAR(7)) AS ADRESS_TEN, STNAME_TEN, CITY_TEN, STATE_TEN,
SUBSTR(ZIP_TEN,1,5) AS ZIP_TEN, MAXDATE
FROM TENANTS T1
WHERE ADRESS_TEN >0 AND MAXDATE >= CURRENT_DATE - :Window AND
MAXDATE = (SELECT MAX(T2.MAXDATE) FROM TENANTS T2
WHERE T1.ADRESS_TEN = T2.ADRESS_TEN AND
T1.STNAME_TEN = T2.STNAME_TEN AND
T1.CITY_TEN = T2.CITY_TEN AND
T1.STATE_TEN = T2.STATE_TEN)
ORDER BY STATE_TEN ASC, CITY_TEN ASC, STNAME_TEN ASC, ADRESS_TEN
ASC, MAXDATE ASC
INTO id, mgrid, adress_ten, stname_ten, city_ten, state_ten,
zip_ten, maxdate
DO
SUSPEND;
END ##
SET TERM ; ##