Page 1 of 1

ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Tue 30 May 2017 08:53
by Butzei
Hi,

I have another problem while moving from NHibernate to DotConnect with EfCore. We have DotConnect 9.4.280 running against an existing Oracle 11g Database.

Now I have trouble with joining some entities. I have a ReportBox with one to one mapping to a ReportVersion, which belongs to one Report. So one ReportBox has one ReportVersion with one Report.

If I try to get all of these together I get an ORA-00918: column ambiguously defined. As you can see in the following SQL query some columns on different tables have the same name, but aren't renamed in the query.

Code: Select all

SELECT "x".ReportBoxId,
       ...
       "x".ReportVersionId,
       "x.reportversion".ReportVersionId,
       "x.reportversion".ReportBoxId,
       "x.reportversion".ReportId,
       ...
       "x.reportversion.report".ReportId,
       ...
FROM   TblReportBox "x"
       LEFT JOIN TblReportVersion "x.reportversion"
              ON "x".ReportBoxId = "x.reportversion".ReportBoxId 
       LEFT JOIN TblReport "x.reportversion.report"
              ON "x.reportversion".ReportId = "x.reportversion.report".ReportId
WHERE  EXISTS
       ( SELECT 1 FROM   TblReportBoxSequence "s"
         WHERE  ( "s".berichtboxsequenzid = :p__boxSequenceId_0 ) AND  ( "x".ReportBoxId = "s".ReportBoxId) )
ORDER  BY "x".ReportBoxId,"x.reportversion".ReportBoxId
FETCH first 2 ROWS only 
Did I miss any configuration?

Sincerely,
Butzei

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Wed 31 May 2017 16:38
by Shalex
Please upload a small test project with the corresponding DDL/DML script for reproducing to our ftp server (ftp://ftp.devart.com/, credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there.

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Thu 01 Jun 2017 13:29
by Butzei
@Shalex: I uploaded a "Butzei_Devart.Demo.zip" containing a example solution to your server. Thank you for your investigations.
It's in the same example as viewtopic.php?f=1&t=35470

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Fri 02 Jun 2017 16:11
by Shalex
Thank you for the test project. Here is SQL generated by your code (option 1):

Code: Select all

SELECT "x".NAME, "x".PARENTID, "x".POSITION, "x.Parent".NAME
FROM CHILD "x"
INNER JOIN PARENT "x.Parent" ON "x".PARENTID = "x.Parent".PARENTID
WHERE ROWNUM <= 2
It works without any errors.

1. Please specify SQL generated in your environment with the test application.
2. Tell us the version and edition of your Oracle Server.

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Tue 06 Jun 2017 07:17
by Butzei
Thank you shalex the query is exactly the same. My oracle version is 12.1.0.2.0 Standard Edition.
I just noticed that the query works if I try it using SQL Developer. In the output table "x.Parent".NAME is named "NAME_1".
Do you know any way to configure this automatic renaming? I can't find anything like that. All working solutions to this problem i found contain manual renaming using the AS keyword.

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Thu 08 Jun 2017 11:50
by rambam
You can simply use aliases for each column, or simply for the one ambiguously defined column. Like this:

Code: Select all

select cld.name  child_name
,      pnt.name  parent_name
from          t_child cld
   inner join t_parent pnt
           on cld.parent_id = pnt.parent.id
which is basically the same as using AS, as you already mentioned.. And now I am slowly waking up... you're talking about a query that DevArt generated for you, right? Then, I cannot help you. Know a lot about Oracle queries, but almost nothing about DevArt.

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Fri 09 Jun 2017 14:25
by Butzei
Hi rambam, nice to see you waking up. You definitely made my day :-D Thanks for your effort

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Fri 09 Jun 2017 20:15
by Shalex
Butzei wrote:My oracle version is 12.1.0.2.0 Standard Edition.
Thank you for the additional information. We have reproduced the "ORA-00918: column ambiguously defined" error. We will notify you when it is fixed.

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Mon 12 Jun 2017 08:24
by Butzei
Thank you!

Re: ORA-00918: column ambiguously defined while joining tables with duplicate column names

Posted: Wed 28 Jun 2017 12:05
by Shalex
The bug with throwing "ORA-00918: column ambiguously defined" while joining tables with duplicate column names in Oracle 12c when using EF Core is fixed: viewtopic.php?f=1&t=35592.