Thanks. OK. Each vehicle is associated(when assigned to) with a contract. My
SQL fetches all records for each vehicle if the period fetched fits into the
contract period supplied. If not, the vehicle is not returned. So, if the
record in the vehicleContract table fits into the current period I am
interested in, it is returned, as well as those with no records(null
values). Problem is if the vehicle has been contracted out to a period not
between the supplied period, the record simply is not returned. I need this
record returned as well.

I could fetch 2 tables, 1 containing all vehicles(from vehicle table), and,
another containing records which fit the bill, left joined. I would like 1
result set returned, with those vehicles not having any matches(as null
values) as well as those having matches in another period. I hope this makes
some sense. The following sql does not return the records having matches not
in the period of concern.:-

select forklift.*, forkliftcontract.oid as forkliftcontractoid ,
forkliftcontract.fllevel, forkliftcontract.colorvalue,
forkliftcontract.fromdate, forkliftcontract.todate, forklift.oid as
forkliftoid, Forkliftcontract.fromdate as ContractFromDate,
Forkliftcontract.Todate as ContractToDate
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

Excuse the formatting.

Many thanks,
Dion

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: 08 January 2006 11:33 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Cursor

> Dion wrote:
>
>>How do I join 2 tables in a stored procedure using firebird. Assigning
>>cursors to variables and doing a join works in MSQL. Can I do this in FB?

I replied:

> Cursors are part of stored procedures in V2. But why not just use the
> normal join syntax?
>

Dion wrote:

>
> No can do. Where can I get the syntax for cursor declaration and use. I am
> using V2.

Just as feedback to the developers, why can't you use a normal SQL join?
If there's a problem, perhaps it should be addressed more generally.


In a more helpful line, you should check the V2.0 release notes for
"Explicit Cursors" - here's a section of the article:

Syntax:
DECLARE [VARIABLE] <cursor_name> CURSOR FOR ( <select_statement> );
OPEN <cursor_name>;
FETCH <cursor_name> INTO <var_name> [, <var_name> ...];
CLOSE <cursor_name>;


Examples
1.
DECLARE RNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS );
BEGIN
OPEN C;
WHILE (1 = 1) DO
BEGIN
FETCH C INTO :RNAME;
IF (ROW_COUNT = 0) THEN
LEAVE;
SUSPEND;
END
CLOSE C;
END


Regards,


Ann



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links






--
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


--
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