🚀 go-pugleaf

Uzytkownik "Johan Anderlund" <johan@anderlund.net> napisal w wiadomosci 
news:4878eaf0$1@newsgroups.borland.com...
> Hi,
>
> 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

use Distinct keyword in select - to return only one unique row

Karol Bieniaszewski