Hi,

I've recently converted an old application from Paradox to Firebird
(using the BDE). I just ran across a problem with the following query:

SELECT PR.Code AS VarCode, PR.Libelle AS Var, C.Variete AS CodeVar,
C.Calibre AS Calibre, C.SousCalibre AS SousCalibre, Cal.OrdreTri,
C.Categorie AS Categorie, Cat.OrdreTri
FROM Corpsfac C
JOIN Entetefa e ON e.code = c.code and e.adherent = c.adherent
JOIN Produits PR ON PR.Code = C.Variete
JOIN calibre cal on (c.calibre = cal.code and
((cal.souscalibre = '' and ((c.souscalibre is null) or (c.souscalibre =
''))) OR
c.souscalibre = cal.souscalibre))
JOIN categor cat on (c.categorie = cat.code)
WHERE e.Date_fact >= '01/01/01' AND e.Date_fact < ('01/01/10' + 1)
AND C.Variete like '1AK'
GROUP BY PR.Code, PR.Libelle, C.Variete, C.Calibre,
C.SousCalibre, C.Categorie, Cal.OrdreTri, Cat.OrdreTri
ORDER BY PR.Code, PR.Libelle, C.Variete, C.Calibre,
Cal.OrdreTri, Cat.OrdreTri, C.Categorie

This query returns no rows. If I change the "AND C.Variete like '1AK'"
clause to "AND C.Variete = '1AK'", it returns the correct rows.

The records in the table don't seem to contain any trailing spaces, but
the field type is a char(4), so is it possible that Firebird doesn't
trim the value, and considers that the value doesn't match the where
clause, even though the data itself doesn't contain any trailing space?
I imagine if the field had been a varchar it would have worked...

So I suppose this is just Firebird working "as designed". :) But is
there some simple way of getting the engine to trim the value for me so
as to find the match, other than replacing "like" by "="? (I'd rather
not use a UDF if I can help it.)

Thanks in advance!

--
Best regards,
Jonathan Neve
_______________
CopyTiger - advanced database replicator for Interbase/Firebird!
Web : http://www.microtec.fr/copycat/ct
_______________________________________
CopyCat - database replication components for Delphi/C++Builder!
Web : http://www.microtec.fr/copycat/cc