Lester Caine wrote:

> I have the following CASE statement eight times in a select statement (
> rooms 1 to 8 :) ), and it check if a room is booked, or a member of
> staff is busy for a time slot. It works fine, and response is acceptable
> ( this is a PHP application ) but I would like to tidy things up.
>
> CASE
> WHEN ( ( SELECT STAFF_ID FROM APPOINTMENT
> WHERE APPOINT_DATE = :dateam
> AND APPOINT_TIME = B.ATIME AND ROOM = 1 ) IS NOT NULL )
> THEN ( SELECT SURNAME || ',' || FORENAME FROM STAFF
> WHERE STAFF_ID = ( SELECT STAFF_ID FROM APPOINTMENT WHERE
>
> APPOINT_DATE = :dateam
> AND APPOINT_TIME = B.ATIME AND ROOM = 1 ) )
> WHEN ( ( SELECT STAFF_ID FROM APPOINTMENT
> WHERE APPOINT_DATE = :dateam
> AND APPOINT_TIME = B.ATIME AND STAFF_ID = $staff )
> IS NOT NULL )
> THEN 'booked'
> ELSE 'book button'
> END AS BK1

Having ruminated on this, I think I have spotted an alternative tidy
using joins.
Replace the
SELECT STAFF_ID FROM APPOINTMENT
WHERE APPOINT_DATE = :dateam
AND APPOINT_TIME = B.ATIME AND ROOM = 1
with eight joins on APPOINTMENT

And the
SELECT STAFF_ID FROM APPOINTMENT
WHERE APPOINT_DATE = :dateam
AND APPOINT_TIME = B.ATIME AND STAFF_ID = $staff
with a single join

So I get
CASE
WHEN ( R1.STAFF_ID IS NOT NULL )
THEN ( SELECT SURNAME || ',' || FORENAME FROM STAFF
WHERE STAFF_ID = R1.STAFF_ID )
WHEN ( SS.STAFF_ID IS NOT NULL ) THEN 'booked'
ELSE 'book button'
END AS BK1

Is that more efficient that stored procedures?

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services