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

Any help ?

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

Thank you

Duilio Foschi

>I am using D3 and IBO3.
>
> with TIBOQuery.Create(Self) do
> try
> databaseName:='cb4db';
> sql.Add('select');
> sql.Add('y_mov,');
> sql.Add('d_mov,');
> sql.Add('n_mov,');
> sql.Add('tip_doc,');
> sql.Add('n_doc,');
> sql.Add('d_doc,');
> sql.Add('note,');
> sql.Add('serie,');
> sql.Add('prezzo,');
> sql.Add('qta,');
> sql.Add('um,');
> sql.Add('cod_acc');
> sql.Add('from mv_hd, mv_dl, mv_li, mv_dg, art'); {1}
> Prepare;
> ...
> finally
> Free;
> end;
>
>When the Prepare command is executed, I get the error "column not

found

>MV_HD".
>
>It seems that IBO changes the code above adding the following line

to {1}

>
>mv_hd, mv_dl, mv_li, mv_dg, art.rdb$db_key
>
>Why does IBO do that and what's wrong with my code ?

We'll start with the second question first.

This is not a valid SQL statement. It is a SQL-89 JOIN statement but
it is
missing (a) the join criteria and (b) the table identifiers from all
of the
columns in the select list.

With IBO, even when you correct the SQL, using SQL-89 joins is not
recommended. You should use the SQL-92 syntax,
select a.col1, b.col2, c.col3
from atable a
join btable b on a.somecolumn = b.somecolumn
join ctable c on......

If you use the SQL-89 syntax, you need to provide JoinLinks for IBO
to tell
it which columns in the WHERE clause are for joining.

Now, the second question: why is IBO doing what it is doing?

IBO is attempting (unsuccessfully) to parse an invalid statement. It
has
included rdb_key in the select list because either a) there are no
keylinks
supplied or b) the supplied keylinks are not valid; and probably the
comma
following the name of the first table is causing the ISC error.

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
--- End forwarded message ---