Hi Helen

Thanks for your help as always, but I am still a little confused.

In your reply you added a For loop. The procedure I attempted to
compile is as posted below. When I attempt to compile this I recieve a
character unknown error ; on line 28 (the end of the into line).

The only way that I can make the procedure compile is to add a do
suspend to the first loop. This however, has some strange side effects
and ends up returning a NULL line when executing the query. Is there
anyway that I can define my variables to be not null?

I suppose I'm just really struggling with why my procedure is
returning a NULL row with the initial select when there is only 1 row
that is returned when I run the query in the bowser.

Thanks

CREATE PROCEDURE GET_FORM_DATES (STUDENT_ID_IN INTEGER)
RETURNS (STUDENT_ID INTEGER, START_DATE DATE, END_DATE DATE)
-------------------------------------------------------------
-- THIS RETURNS THE STUDENTS TRUE START AND END DATE ---
-------------------------------------------------------------
AS
DECLARE VARIABLE SF_START_DATE DATE;
DECLARE VARIABLE TE_START_DATE DATE;
DECLARE VARIABLE SF_END_DATE DATE;
DECLARE VARIABLE TE_END_DATE DATE;
BEGIN
FOR
SELECT
MAX(TE.END_DATE), MIN(TE.START_DATE), MAX(SFR.START_DATE),
MIN(SFR.END_DATE), SFR.STUDENT_ID
FROM
STUDENT_FORM_RUN SFR,
FORM_RUN FR,
TERM TE
WHERE
SFR.STUDENT_ID = 399
AND SFR.FORM_RUN_ID = FR.FORM_RUN_ID
AND FR.TIMETABLE_ID = TE.TIMETABLE_ID
AND SFR.START_DATE IS NOT NULL
GROUP BY 5
INTO
:TE_END_DATE, :TE_START_DATE, :SF_START_DATE, :SF_END_DATE,
:STUDENT_ID;
DO
BEGIN
END_DATE = COALESCE(:SF_END_DATE, :TE_END_DATE);
START_DATE = COALESCE(:SF_START_DATE, :TE_START_DATE);
SUSPEND;
END
END!!