Is it possible to do a join on a cursor as follows?



SET TERM ^ ;



CREATE PROCEDURE GETAVAILABLESLOTS (

STARTDATE TIMESTAMP,

ENDDATE TIMESTAMP)

RETURNS (

FORKLIFTOID INTEGER)

AS

DECLARE AVAILSLOTS CURSOR FOR (select forklift.oid

from forklift



left join forkliftcontract on (forkliftcontract.forkliftoid =
forklift.oid)

where ( ((forkliftcontract.fromdate <= :startdate) and
(forkliftcontract.todate >= :Enddate)) or

((forkliftcontract.fromdate <= :startdate) and
((forkliftcontract.todate <= :enddate) and (forkliftcontract.todate >=
:startdate))) or

((forkliftcontract.fromdate >= :startdate and
forkliftcontract.fromdate <= :enddate) and

(forkliftcontract.todate >= :startdate and forkliftcontract.todate
<= :enddate)) or

(((forkliftcontract.fromdate >= :startdate) and
(forkliftcontract.fromdate <= :enddate)) and (forkliftcontract.todate >=
:enddate)) )

or ((forkliftcontract.fromdate is null) and
(forkliftcontract.todate is null))

order by forkliftcontract.contractoid, forkliftcontract.fllevel);



begin



/* Get a result set of all available slots */

/* open AvailSlots; */

for

select oid from forklift f

left join availslots a on (a.forkliftoid = f.oid)

into :Forkliftoid

do

begin

suspend;

end

/* fetch availslots into :forkliftoid; */

/* suspend; */

/* Join this with the table of forklifts, as the above result set does
not */

/* contain those forklifts with slots outside of the current contract
period */



end^



SET TERM ; ^



Regards,

Dion.




--
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.14.7/214 - Release Date: 2005/12/23



[Non-text portions of this message have been removed]