Error #23000 Column 'XYZ' in where clause is ambigious

swierzbicki · Post by **swierzbicki** » Tue 20 Jun 2006 16:04

Hi,

I guess I found another bug with the Master / Detail function :

Create 3 Tables

MasterTable :

MasterId : Integer (autoinc)
Caption : Varchar 10

DetailTable:

DetailId : integer (autoinc)
MasterId : integer
Caption : varchar 10

JoinTable:

JoinTableId : integer (autoinc)
DetailId : integer
Prize : double (15,2)
MasterId : Integer (autoinc)
Caption : varchar 10

On a datamodule add 2 queries : one named QrMaster, the other one named QrDetails.

On the QrMaster SQL :

Select * From MasterTable

On the QrDetail SQL :

Select DetailTable.*, JoinTable.* From DetailTable Left OuterJoin JoinTable on (DetailTable.DetailId = JoinTable.DetailId ) where DetailTable.prize 0 Order by DetailTable.Caption

Set the mastersource property + details / Master fields (in this case to MasterId).

Now try to open the Query : you will get an Error #23000 Column 'MasterId' in where clause is ambigious...

If I look with the DBMonitor , In can see that :

Select DetailTable.*, JoinTable.* From DetailTable Left OuterJoin JoinTable on (DetailTable.DetailId = JoinTable.DetailId ) where ( DetailTable.prize 0 ) And MasterId = : MasterID Order by DetailTable.Caption

This should be "DetailTable.MasterId = :MasterId"...
I've also tried to modify my where clause (by adding the "DetailTable.MasterId = :MasterId". This didn't worked since the component already added a new clause : "And MasterId = :MasterId"

When joinning the key, isn't it possible to select from witch table the Detail key will be taken ? ( having a drop down list from the selected table like you did in the "Generate insert/Delete/update/refrehs SQL Statement wizard" ) or even use the "UpdatingTable" property ?

swierzbicki · Post by **swierzbicki** » Tue 20 Jun 2006 16:33

I've found a workarround (but this is not valuable) :
I've removed the DetailKey property. So, only the MasterSource and MasterKey are filled.
I've edited my Detail Query and added this to my Where Clause :

AND DetailTable.MasterID = :MasterID

Now I can open / close my query without any issues... But as said this is a temporary solution as it is not really clean.

Antaeus · Post by **Antaeus** » Wed 21 Jun 2006 10:04

Our SQL generator can't work with such complicated queries. To achive correct Master/Detail relationship in this situation you should manually set correct query statement. The solution you have found is correct.

swierzbicki · Post by **swierzbicki** » Wed 21 Jun 2006 12:09

Hi Antaeus,

It will be nice to add further enhancements to the master/Detail property, what about this one :

When joinning the keys, isn't it possible to select from witch table the Detail key will be taken ? ( having a drop down list from the selected table like you did in the "Generate insert/Delete/update/refrehs SQL Statement wizard" ) ?

Antaeus · Post by **Antaeus** » Wed 21 Jun 2006 15:13

We don't think the enhancements you offer will be very useful.
However, we have added this feature to users wish-list, so it may be implemented in future versions if there will be more demand for the feature.

swierzbicki · Post by **swierzbicki** » Wed 21 Jun 2006 18:14

Thank you antaeus