Column ambiguously defined and Master detail

Valgardur · Post by **Valgardur** » Wed 17 Feb 2010 13:55

I occasionally encounter an Ora-00918 error "column ambiguously defined" when using master detail queries. I have read a similar post from 2007, but it does not seem to be the same problem.

If I join two tables on the detail level, sometimes it works fine, fx. to use a simplified version:

select d.year, d.month, n.name
from dates d, names n
where date = :date
and d.date = n.date

works fine.

However sometimes I get the ambiguous error, and my guess is that when the tables joined in the query have columns with the same name, even though qualified, this error occurs, for example if in my query the table names has a field called month.
[/quote]

Challenger · Post by **Challenger** » Thu 18 Feb 2010 13:05

ODAC adds fields to the WHERE clause of the detail dataset without table aliases. That is why if the DetailFields property contains the "date" field you will get the specified error. You can clear the MasterFields and DetailFields properties and write the detail SQL statement manually.

Valgardur · Post by **Valgardur** » Thu 18 Feb 2010 13:20

Thanks for a quick reply, but I believe there is some misunderstanding regarding my problem.

I have four queries, all details to the same master query, two of them need to be joined by two tables, one of them works, the other doesn't.

In my example the "date" field is present in both queries and not the problem.

I am writing the detail SQL statement manually in both cases.

Best wishes,

Valgarður Guðjónsson

Challenger · Post by **Challenger** » Thu 18 Feb 2010 13:33

Please send us a small sample or these four queries to support*devart*com.

Valgardur · Post by **Valgardur** » Wed 03 Mar 2010 12:45

I had to solve this by circumventing the problem, I'm not allowed to send the code in question, and I have been unable to produces this in a small project.. I guess I'll stick with fixes, at least for a while...

Best wishes,

Valgarður Guðjónsson