At 07:53 AM 5/03/2005 +0000, Brendan Donoghue wrote:

>Hello
>
>I have written the below stored procedure. When I run the select by
>itself I get 1 row returned. Yet when I compile and execute the
>procedure I get thIS error :
>
>SQL> EXECUTE PROCEDURE GET_FORM_DATES(399);
>Statement failed, SQLCODE = -811
>
>multiple rows in singleton select
>
>Can anyone shed any light on why I am getting this error?

Yes, potentially it is not a singleton select, even though you know it
is! In PSQL you have to take care of this possibility by putting the
select statement into a FOR loop.

See corrections inline ---

>Thank you very much.
>
>
>
>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!!

The SUSPEND is required to output the contrived row[s] the buffer, where
the client is waiting for it [them] from a SELECT call to the procedure.

Also, even if you *know* (or believe you know) that this SP could never
actually return multiple rows, you should take care to initialise variables
at the start of the loop if nulls are a possibility (as suggested by your
coalescing to try to get non-null start and end dates from the grouping).

./hb