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