At 05:41 AM 7/03/2005 +0000, you wrote:

>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.

Erk, no! a DO BEGIN <DO_STUFF> END block is the looping part of a FOR
SELECT.. construct. You can't slot bits of one syntax into another and
hope not to offend the gods of the SQL engine.

I'll try to sort out the SQL this but I don't guarantee it gets you what
you want. Gotta say this looks like a relational bird's nest to me
(sorry!) so I won't even start to guess.

>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;

Right, the above will work *only* if it would be theoretically impossible
to return more than one row from it. Note also that, if it would not
return any rows, you have provided no escape route.

>SELECT
> NUMBER
>FROM
> GET_WORKING_DAYS(:TE_CYCLE_START_DAY,:FOR_DATE)
> INTO :DAYS_DIFF; /* added a terminating colon */

Note, still no protection here from null results.

>SELECT
> DISPLAY_DAY
>FROM
> CYCLE_DIVISOR
>WHERE
> DIVISOR_RESULT = MOD(:DAYS_DIFF,:CY_DAYS_IN_CYCLE)

> AND :CY_DAYS_IN_CYCLE = DAYS_IN_CYCLE

Hope this comparison works.

> INTO :RESULT_DISPLAY_DAY ; /* added colon terminator */

/* Not valid (there is no loop running)

>DO BEGIN

OK, do you expect to get multiple rows here? */

for

> 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

do

>SUSPEND;
>END!!

Also - be a sport, won't you, and trim your replies? Otherwise, you risk
offending the practitioners of the Occult Arts who moderate this list...you
need SOMEONE on side!

./hb