Subject problem - using expression in JOIN
Author Radovan Bukoci

Please, I have trouble to run this query:

SQL> select cisloof, OSTAVAZAP, FORMAUHRADY, sum(prijem) AS prijem1 FROM
a_OdbFak LEFT JOIN Uhrady_do_20046
CON> ON 'OF' + a_OdbFak.CISLOOF = Uhrady_do_20046.PREPOJKOD WHERE
CON> GROUP BY cisloof, ostavazap, formauhrady;

getting error:

Dynamic SQL Error
-expression evaluation not supported

I think the trouble is in
... ON 'OF' + a_OdbFak.CISLOOF = Uhrady_do_20046.PREPOJKOD ...

(by which I want to say, that, for example, value 123456 in one table
should join with value 'OF123456' in second table.)

I could put that condition into WHERE clause, if it was INNER JOIN, but
with LEFT JOIN I would loose some records.

I have Firebird 1.5.0 on Linux.


1. Does Firebird not support JOINs with expressions ? (In fact i am
moving from MS Access, where it works, but I think in some DBs like
Sybase or Oracle it works too).
2. How can solve it ? Build a procedure ? Can this procedure be then
used in a "SELECT ... GROUP BY" statement ?
3. I can workaround it also by creating a new column in the first or
second table with, or without the 'OF' string and using an ordinary
JOIN, but since moving from access I am way too often working something
around, so I am asking for other way(s) to do it.

Thank you