How to do this selection?
Johan Anderlund
I have huge problems figuring out how to write my stored procedure,
which will return all times in a day (SELDAY) a staff (ID_STAFF) is free
after a selected time (START_TIME). Free is the same as not having
anything booked in my application. STIME is the length of the selected
treatment.
I want it too return something like this:
32
33
36
37
38
42
...
The time is numbers of quarters, so 32 is the same as 32 / 4 = 8:00.
The problem is when a staff has something booked, lets say for time 32
and 33. I will then get this error: multiple rows in singleton select.
The select statement in my while loop (select BD.ID_BOOKING) will return
the same ID_BOOKING both when I is 32 as when I is 33.
This is the stored procedure:
ALTER PROCEDURE "WEB_FREETIMES"
(
"ID_SERVICE" INTEGER,
"SELDAY" DATE,
"START_TIME" INTEGER,
"ID_STAFF" INTEGER
)
RETURNS
(
"FREE_TIME" INTEGER
)
AS
DECLARE VARIABLE "SERVICE_TIME" INTEGER;
DECLARE VARIABLE "I" INTEGER;
DECLARE VARIABLE "NCOUNT" INTEGER;
DECLARE VARIABLE "OPENING_END" INTEGER;
DECLARE VARIABLE "SHIFT_END" INTEGER;
BEGIN
select STIME
from SERVICE
where ID_SERVICE = :ID_SERVICE
into :SERVICE_TIME;
select SHIFT_END
from SHIFT
where ID_STAFF = :ID_STAFF
and SHIFT_DATE = :SELDAY
into :SHIFT_END;
if (SHIFT_END is null) then
SHIFT_END = 0;
I = START_TIME;
WHILE (I <= SHIFT_END) DO
BEGIN
IDBOOKING = 0;
select BD.ID_BOOKING
from BOOKING_DETAIL BD
left join BOOKING B on (BD.ID_BOOKING = B.ID_BOOKING)
where BD.ID_STAFF = :ID_STAFF
and B.BOOKING_DATE = :SELDAY
and (BD.TIME_BEGIN between :I and :SERVICE_TIME + :I - 1
or BD.TIME_BEGIN + BD.TIME_LENGTH - 1 between :I and
:SERVICE_TIME + :I - 1)
INTO :IDBOOKING;
IF (!IDBOOKING > 0) THEN
BEGIN
FREE_TIME = :I;
SUSPEND;
END
I = I + 1;
END
END;
Anyone can help me?
// Johan
Karol Bieniaszewski
Uzytkownik "Johan Anderlund" <jo...@anderlund.net> napisal w wiadomosci
news:4878eaf0$1...@newsgroups.borland.com...
use Distinct keyword in select - to return only one unique row
Karol Bieniaszewski