Why doesn't this SQL work?

Hi,

Just converting a small application from 16-bit delphi to 32-bit
delphi. I've found a couple of SQL constructs that work in the
16-bit BDE, but not in the 32-bit BDE (querying the same Paradox
tables in both cases).

The first one is ...

select b.BugNumber,
       p.Description,
       b.Version,
       b.Title,
       s.Status,
       b.OtherRefs
from bugs b, bugnotes nl, products p, status s
where b.Product = p.ProductCode
  and nl.BugNumber = b.BugNumber
  and nl.NoteNumber = b.LastNote
  and s.StatusCode = nl.Status
  and b.Product = 2
  and ( b.OtherRefs like "Value%" )
  and ( s.Status in ("New","Work") )
order by b.BugNumber ascending

The problem appears to be with the combination of "like" and "in"
clauses - take either out and the statment works.

The second statement looks like this ...

select b.BugNumber,
       p.Description,
       b.Version,
       b.Title,
       s.Status,
       b.OtherRefs,
       nl.Engineer
from bugs b, bugnotes n0, bugnotes nl, products p, status s
where b.Product = p.ProductCode
  and n0.BugNumber = b.BugNumber
  and n0.NoteNumber = 0
  and nl.BugNumber = b.BugNumber
  and nl.NoteNumber = b.LastNote
  and s.StatusCode = nl.Status
  and n0.Engineer = "dave"
order by b.BugNumber ascending

In this case the problem appears to be with the attempt to select
2 different records from the same table (n0 and nl)

In both cases, the 16bit BDE gives the expected results, while the
32bit BDE silently fails, returning an empty result set.

--
David Haworth                      d...@tigcomms.demon.co.uk
Tiger Communications         http://www.tigcomms.demon.co.uk
------------------------------------------------------------
Get off your horse and drink your milk