At 07:49 PM 21-03-02 +0100, you wrote:

>I have a new problem with the JOIN command.
>
>This time it is a strange one.
>
>This code compiles ok on my computer.
>
>
> with SecTab do
> begin
> DatabaseName:='cb4db';
> FetchWholeRows:=False;
> sql.Clear;
> sql.Add('select');
> sql.Add('a.partita,');
> sql.Add('a.y_mov,');
> sql.Add('a.n_mov,');
> sql.Add('a.n_row,');
> sql.Add('a.us,');
> sql.Add('a.p_netto,');
> sql.Add('a.cod_cau, ');
> sql.Add('a.qta, ');
> sql.Add('b.dst_des,');
> sql.Add('b.dst_cit');
> sql.Add('from mv_li a');
> sql.Add('join bl_pr b on b.y_mov=a.y_mov and b.n_mov=a.n_mov');
> sql.Add('where');
> sql.Add('mv_li.partita=:partita');
> sql.Add('and');
> sql.Add('mv_li.cod_art=:cod_art');
> sql.Add('and');
> sql.Add('mv_li.cod_cau="VEN"');
> sql.Add('and');
> sql.Add('mv_li.d_mov>=:d1');
> sql.Add('and');
> sql.Add('mv_li.d_mov<=:d2');
> sql.Add('and');
> sql.Add('mv_li.cod_mag=:cod_mag');
> Prepare;
> end;
>
>
>However, it does not compile on a second computer that has (or should have)
>the same D3/IBO3 versions.
>
>The error message is "unknown token JOIN".

Duilio,
If something won't compile, that is an issue in the Delphi area of your work.

I'm assuming that the error message you refer to is a run-time error, part
of an exception when you try to open the dataset? The reason for that part
of your problem is that your SQL is still not correct.

I will laboriously reconstruct your statement from this strange code setup
you are using....why do you do this, adding one word at a time to the SQL
property?

Here is your statement:

select a.partita, a.y_mov,a.n_mov, a.n_row, a.us,
a.p_netto,a.cod_cau, a.qta,
b.dst_des, b.dst_cit
from mv_li a
join bl_pr b on b.y_mov=a.y_mov and b.n_mov=a.n_mov

/* so far, so good */

where mv_li.partita=:partita
and mv_li.cod_art=:cod_art
andmv_li.cod_cau="VEN"
and mv_li.d_mov<=:d2
and mv_li.cod_mag=:cod_mag

Two SQL errors in this section:

1. Strings should NOT be in double quotes - although, as I recall, you are
using IB 4.x or 5.x, where this will not cause an SQL exception.

2. Having used table aliases in the the SELECT clause, you must also use
them in the WHERE clause:

where a.partita=:partita
and a.cod_art=:cod_art
/* and a.cod_cau="VEN" */
and a.cod_cau='VEN'
and a.d_mov<=:d2
and a.cod_mag=:cod_mag

I strongly recommend that

1. you test your statements in IB_SQL before you put them inside your code
- that way, it makes it so much easier to debug your SQL and separate your
SQL bugs from your Delphi bugs. It also means that you have something more
useful to post to the support lists when things don't work out!

2. You make use of IBO's pre-compilation and run-time parsing, by placing
the SQL into the dataset objects at design time. Then, at run-time, you
need pass only the parameters when the user wants data; and this will save
a LOT of overhead at run-time, in re-preparing the statement.

(Unless you have some special requirement to create data access objects at
run-time, of course....)

>Could the IB client software version be the culprit ? (I didn't check this).

It could, I suppose....if you are using a Firebird gds32.dll over a
mismatched server...because Firebird is much more particular about
unambiguous JOIN statements than any of the IB versions.

Could you please do something about trimming your replies? Thanks.

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com