Hello,

I'm trying to use a stored procedure in a order by clause.
I thought I was doing OK, I am not very fluent in SP's, until I needed a
second parameter.

When I do something like this (I created some simple example to reproduce):

order by (select OUT_TIMESTART from
RESERVATION_START(reservation.RESERVATIONID))

it works OK.

When I change the stored procedure so that it needs 2 parameter (I don't
even use the second parameter yet) I get an error

order by (select OUT_TIMESTART from
RESERVATION_START(reservation.RESERVATIONID,1))

--------
ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, char 81
ASC

Statement: select * from reservation
order by (select OUT_TIMESTART from
RESERVATION_START(reservation.RESERVATIONID,1))
--------

I tried the using the same SP in the select or where clause. There it works
OK.

To reproduce the problem I've added some sql.

Could somebody please tell me what is going on?

Thank you,

Henk van der Meer

------ sql code to reproduce the problem -----

/* Remove comment for second try */

/*
DROP PROCEDURE RESERVATION_START;
drop TABLE RESERVATION;
*/

CREATE TABLE RESERVATION
(
RESERVATIONID INTEGER,
PERSONS INTEGER,
TIMESTART TIMESTAMP
);

insert into reservation values(1,10,'1-8-2004');
insert into reservation values(2,20,'1-7-2004');
insert into reservation values(3,30,'1-6-2004');
insert into reservation values(4,40,'1-5-2004');
insert into reservation values(5,50,'1-4-2004');
insert into reservation values(6,60,'1-3-2004');
insert into reservation values(7,70,'1-2-2004');
insert into reservation values(8,80,'1-1-2004');


COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "RESERVATION_START"
(
"IN_RESERVATIONID" INTEGER
)
RETURNS
(
"OUT_TIMESTART" TIMESTAMP
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "RESERVATION_START"
(
"IN_RESERVATIONID" INTEGER,
"SECONDPARAM" INTEGER
)
RETURNS
(
"OUT_TIMESTART" TIMESTAMP
)
AS

BEGIN
select TIMESTART
from reservation
WHERE reservation.RESERVATIONID = :IN_RESERVATIONID
INTO :OUT_TIMESTART;
SUSPEND;
END
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

select * from reservation
order by (select OUT_TIMESTART from
RESERVATION_START(reservation.RESERVATIONID,1))