At 01:03 AM 2/03/2004 +0000, you wrote:

>Hi Helen
>
>Thank you for your reply!
>
> > At 04:22 PM 1/03/2004 +0000, you wrote:
> > >Hi!
> > >
> > >With IBObjects 4.3Aa I have the following problem. I have a TIB_Cursor
> > >with a SQL
> > >
> > >SELECT * FROM Table1 join Table2 on Table1.ID = Table2.ID
> > >WHERE Table1.ID = :pID
> >
> > The query is ambiguous.
> >
> > select table1.* from table1
> > join table2 on table1.id = table2.id
> >
>
>I and my Firebird 1.5 don't find this query ambigous (no error from
>Firebird). Also your query gives a differnt returnset than my query!
>My query includes all the fields of Table2 too.
>
> > >Anyone has an idea, what I'm doing wrong?
> >
> > First, don't write ambiguous joins.
> > Second, don't use joins that should be WHERE criteria.
> >
> > select table1.* from table1
> > where exists(
> > select table2.id from table2
> > where table2.id = table1.id
> >
>
>No, that's different. I want
>
>select table1.*, table2.* from .....

But you wrote:

> >SELECT * FROM Table1 join Table2 on Table1.ID = Table2.ID
> >WHERE Table1.ID = :pID


>so this exist clause doesn't work I think.

No, it won't work for a cross-join (which is what your "new" query wants).

>Okay, I also thought about the problem, that KeyLinks cannot be
>resolved properly, because of field id, which isn't unique. So I used
>another version of this query:
>
>select Table2.Field21, Table2.Field22, Table1.*
>from table1 join table2 .....
>
>Field21 and Field22 are unique within Table1.
>
>Setting KeyLinks and KeyRelation as described in the mail before. But
>this was still not helping to resolve the KeyLinks correct.

No, the Keylinks won't be correct unless you provide the correct Keylinks.
:-) For this query, as a minimum (for uniqueness), you would need:
Table2.Field21
Table2.Field22
Table1.ID

However, these KeyLinks are still no good to you if your KeyRelation is
Table1, because Table1.ID is not unique **in Table1**. It can't be used
for a positioned update.

The default DML methods of the dataset *must* find one and only one
row. If you want the DML to update multiple rows then you have to use
xxxxSQL, or
---- take it out of the dataset altogether, don't call any DML methods from
the dataset but invoke it in a TIB_DSQL.

>I know that I could write EditSQL but I would like to avoid in this
>situation because IBObjects has quite clever logic inside. The
>Update-Statement is only updating the field with changed values, etc.

The logic is clever provided the KeyLinks are sound. But you are not
providing a set that IBO can perform DML on, so far. IBO is great with
logic, but it's not so good at alchemy.

Helen