Database reader node issues - ORA-00918: column ambiguously defined

I'm using a database reader node to read an Oracle database, and it is all set up ok, it can read the database fine.

However it is throwing an Oracle error for the specific query I am trying to run.  The error is this:

WARN      Database Reader     java.sql.SQLException: ORA-00918: column ambiguously defined

It is correct that I am querying across multiple tables and that within each of those tables is a column which has the same name (LUID), but I am defining my table names for each column, so my columns are not ambiguous.  The proof of this is that I can run the SQL query in SQL developer without a problem, so the SQL would seem to be ok.  It seems to be Knime which has the issue and I'm not sure how to proceed.

I have upgraded to 64bit 2.7.2 today in case the fact I was running an old version was the problem, but that has not made a difference.

This is my SQL query:

SELECT A.STRUCTURE, A.SUBST_ID, B.REPORT_NUMBER, B.YEAR, B.RECOVERY, B.EXPOSURE_PERIOD, B.DOSAGE, C.DOSE, C.UNIT1, C.CLINICAL_CHEMISTRY_PARAMETER, C.FINDING, C.TIMEPOINT, C.TIMEPOINT_UNIT FROM DB_PROD.SUBSTANCE_IDS A, DB_PROD.STUDY_DESIGN B, DB_PROD.CLINICALCHEMICALFIN C where A.LUID=B.STRUCTURE_LUID and B.LUID=C.PARENT_LUID and A.SUBST_ID = 'E-1234'

Any help much appreciated, I am very new to Knime, and not particularly experienced with SQL.

Thank you, Emma

 

Since this looks like an official Oracle error which is described here, I guess it has something to do with column names in your query not using alias'. It seems that the underlying driver need them. I am not 100% how to fix it, but it might help googling for it...

I had this error and I had to change my SQL statement. In the select clause when you have two columns a.account, b.account, the SQL statement would run fine but it would show up as error on KNIME. So just change the select part as

select a.account as a_account, b.account as b_account …from … where …

This will resolve the issue

1 Like