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

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

Postby Butzei » Tue 30 May 2017 08:53

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
Butzei
 
Posts: 9
Joined: Fri 26 May 2017 07:41

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

Postby Shalex » Wed 31 May 2017 16:38

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

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

Postby Butzei » Thu 01 Jun 2017 13:29

@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 http://forums.devart.com/viewtopic.php?f=1&t=35470
Butzei
 
Posts: 9
Joined: Fri 26 May 2017 07:41

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

Postby Shalex » Fri 02 Jun 2017 16:11

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

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

Postby Butzei » Tue 06 Jun 2017 07:17

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.
Butzei
 
Posts: 9
Joined: Fri 26 May 2017 07:41

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

Postby rambam » Thu 08 Jun 2017 11:50

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.
rambam
 
Posts: 1
Joined: Thu 08 Jun 2017 11:38

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

Postby Butzei » Fri 09 Jun 2017 14:25

Hi rambam, nice to see you waking up. You definitely made my day :-D Thanks for your effort
Butzei
 
Posts: 9
Joined: Fri 26 May 2017 07:41

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

Postby Shalex » Fri 09 Jun 2017 20:15

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

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

Postby Butzei » Mon 12 Jun 2017 08:24

Thank you!
Butzei
 
Posts: 9
Joined: Fri 26 May 2017 07:41


Return to dotConnect for Oracle