Hi Helen

Thanks for this. It all makes sense now.

Just one last question what would the syntax be if I was declaring
variables in multiple select statements before the begin?

I have attempted with the procedure below but am having difficulties.

Thanks

CREATE PROCEDURE GET_TIMETABLE ( FOR_DATE DATE )
RETURNS
(
CLASS INTEGER,
TEACHER_ID INTEGER,
STAFF_ID INTEGER,
FORM_RUN INTEGER,
PERIOD_ID INTEGER
)
AS
DECLARE VARIABLE TE_CYCLE_START_DAY DATE;
DECLARE VARIABLE TE_TIMETABLE_ID INTEGER;
DECLARE VARIABLE CY_DAYS_IN_CYCLE INTEGER;
DECLARE VARIABLE DAYS_DIFF INTEGER;
DECLARE VARIABLE RESULT_DISPLAY_DAY INTEGER;
DECLARE VARIABLE FORM_RUN_ID INTEGER;
BEGIN
SELECT
TE.CYCLE_START_DAY,
TE.TIMETABLE_ID,
TE.START_DATE
FROM
TERM TE,
TERM_GROUP TG,
CYCLE CY
WHERE
TE.TERM_ID = TG.TERM_ID
AND TG.CYCLE_ID = CY.CYCLE_ID
INTO
:TE_CYCLE_START_DAY,
:TE_TIMETABLE_ID,
:CY_DAYS_IN_CYCLE;
SELECT
NUMBER
FROM
GET_WORKING_DAYS(:TE_CYCLE_START_DAY,:FOR_DATE)
INTO :DAYS_DIFF
SELECT
DISPLAY_DAY
FROM
CYCLE_DIVISOR
WHERE
DIVISOR_RESULT = MOD(:DAYS_DIFF,:CY_DAYS_IN_CYCLE)
AND :CY_DAYS_IN_CYCLE = DAYS_IN_CYCLE
INTO :RESULT_DISPLAY_DAY
DO BEGIN
SELECT
CLA.CLASS,
CTE.TEACHER_ID,
PCT.TEACHER_ID,
FR.FORM_RUN,
PCD.PERIOD_ID
FROM
CLASS CLA,
CLASS_TEACHER CTE,
TIMETABLE TT,
FORM_RUN FR,
PERIOD_CLASS PC,
PERD_CLS_TEACHER PCT,
PERIOD_CYCLE_DAY PCD
WHERE
CLA.CLASS_ID = CTE.CLASS_ID
AND CLA.ACADEMIC_YEAR_ID = TT.ACADEMIC_YEAR_ID
AND TT.TIMETABLE_ID = FR.TIMETABLE_ID
AND FR.FORM_RUN_ID = :FORM_RUN_ID
AND CLA.CLASS_ID = PC.CLASS_ID
AND PC.PERIOD_CLASS_ID = PCT.PERIOD_CLASS_ID
AND PC.PERIOD_CYCLE_DAY_ID = PCD.PERIOD_CYCLE_DAY_ID
AND PCD.CYCLE_DAY_ID = :RESULT_DISPLAY_DAY
INTO
:CLASS,
:TEACHER_ID,
:STAFF_ID,
:FORM_RUN,
:PERIOD_ID
SUSPEND;
END!!

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 11:02 PM 6/03/2005 +0000, you wrote:
>
>
> >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).
>
> Just remove the semi-colon! It's a statement terminator. The FOR

SELECT

> statement doesn't have one.
>
> The pattern is this:
>
> for select <specs>
> into <vars>
> do
> begin
> <nested statements>
> end
>
> ./heLen