Hi Svein,



You seem to be on the right track. Another question, though, connecting to
the Firebird V2 server, I now get a ‘database unavailable…’ error. The
reason I did not get it previously, is that I found out that I had the V1.5
gds file in the path. I am using a TIBDatabase component. Can these be used
with V2?



Regards,

Dion.





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 09 January 2006 11:06 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Cursor



Hi Dion,
it took a while to understand what your question was. My guess is that
you really want a LEFT JOIN, but no WHERE clause (normally, you would
only use the right table in the WHERE clause if you are looking for
values not in the right table at all - that does not seem to be what
you are looking for).

Try this:

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
and ((forkliftcontract.fromdate <= :startdate and
forkliftcontract.todate >= :Enddate) or
(forkliftcontract.fromdate <= :startdate and
forkliftcontract.todate between :startdate and :enddate) or
(forkliftcontract.fromdate between :startdate and :enddate and
forkliftcontract.todate between :startdate and :enddate) or
(forkliftcontract.fromdate between :startdate and :enddate and
(forkliftcontract.todate >= :enddate))
order by forkliftcontract.contractoid, forkliftcontract.fllevel

I moved everything from the WHERE clause to the JOIN clause, replaced
a few >=..<= with BETWEEN, and removed some parenthesis (moving from
the WHERE to the JOIN clause changes which records the query returns,
the other two just made it easier for me to read).

HTH,
Set

--- In firebird-support@yahoogroups.com, "Dion" wrote:
> 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






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

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

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

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





SPONSORED LINKS


HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw"Technical support

HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA"Computer technical
support

HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g"Compaq
computer technical support


HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w"Compaq technical
support

HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA"Hewlett
packard technical support

HYPERLINK
"http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw"Microsoft
technical support



_____

YAHOO! GROUPS LINKS



* Visit your group "HYPERLINK
"http://groups.yahoo.com/group/firebird-support"firebird-support" on the
web.


* To unsubscribe from this group, send an email to:
HYPERLINK
"mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe"fir
ebird-support-unsubscribe@yahoogroups.com


* Your use of Yahoo! Groups is subject to the HYPERLINK
"http://docs.yahoo.com/info/terms/"Yahoo! Terms of Service.



_____


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



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