Not too sure why, but I find it a good practice to explicitly define
which table each field belongs, even when no ambiguity currently
exists. I never assume a table will not be altered to introduce an
ambiguity.

So my SQL would look something like:

select P.PersonnelNo
from Passenger P
inner join Flight F on P.FltSeqNo = F.FltSeqNo
where F.FltDate = current_date
and F.DepartFrom = ?

Adam


--- In firebird-support@yahoogroups.com, "davidalbiston"
<dalbiston@c...> wrote:

>
> FB does not catch ambiguous column names in a sub-query. For

example,

> I want find all passengers who are boarding flights on a certain

date

> at a certain point on the route:
>
> select PersonnelNo
> from Passenger P
> inner join Flight F on P.FltSeqNo = F.FltSeqNo
> where FltDate = current_date
> and DepartFrom = ?
>
> There is a DepartFrom column in both tables so FB raises an

exception.

> However if I have the same SQL in a sub-query there is no objection:
>
> select ...
> where PersonnelNo in
> (select PersonnelNo
> from Passenger P
> inner join Flight F on P.FltSeqNo = F.FltSeqNo
> where FltDate = current_date
> and DepartFrom = ?)
>
> A small point but I am converting form InterBase and am getting
> paranoid about ambiguous column names.
>
> Dave