If T1 and T2 both contain a field "Total" then in this query

select *
  from T1
  join T2 on T1.ID = T2.ID
 where Total > 0

the reference to Total is ambiguous.  DBISAM chooses the field from the
table that appears first in the query.  This is reasonable, and I can
see that changing it would cause problems for people.  What I'm
wondering is: how can I detect it?  Is there an easy way to get a list
of all columns involved in the query, not just those that appear in the
SELECT?

(I have a situation where I dynamically build SQL, and this problem can
be difficult to notice and can be dangerous as I might get the wrong
field.  Always using table references like T1.Total would be onerous.)


  Oliver

"Oliver Bock" <oliver-elevatesoft-news@ikucwe.com> wrote in message
news:5717C2E3-6488-4183-BC1A-2AC81AC5C90C@news.elevatesoft.com...
> field.  Always using table references like T1.Total would be onerous.)
>

That is exaclty what Shazam and others do when building SQL. It makes the
SQL more self documenting, and avoids the abiguity.

Robert

Oliver Bock <oliver-elevatesoft-news@ikucwe.com> wrote in news:5717C2E3-
6488-4183-BC1A-2AC81AC5C90C@news.elevatesoft.com:

> If T1 and T2 both contain a field "Total" then in this query
>
> select *
>    from T1
>    join T2 on T1.ID = T2.ID
>   where Total > 0
>
> the reference to Total is ambiguous.  

perhaps a refusal to prepare if fieldnames are ambiguous could be added as
a database option, defaulting to false for backward compatibility?
--
Chris

Oliver,

<< the reference to Total is ambiguous.  DBISAM chooses the field from the
table that appears first in the query.  This is reasonable, and I can see
that changing it would cause problems for people.  What I'm wondering is:
how can I detect it?  Is there an easy way to get a list
> of all columns involved in the query, not just those that appear in the
> SELECT? >>

Chris has a good idea - we might be able to add a property that prevents
such constructs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Chris has a good idea - we might be able to add a property that prevents
> such constructs.

That would be excellent.


  Oliver