org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause
When trying to load a table using and interface I got the error: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "analysis_groups"
Hint: Perhaps you meant to reference the table alias "ANALYSIS_GROUPS".
I'm moving data from PortgresSQL to Oracle.
LKM SQL to Oracle
IKM Oracle Incremental Update
I was able to create the Postgresql model on ODI using reverse engineer. I recreate the model in Oracle using the DDL available for Postgresql with some modifications.
Automapping didn't work which I thought was odd since the columns names are the same. Also when dragging and dropping the source to the target for mapping I had problems and only worked after second attempt on each column.
To find the cause I navigate to the step under Operator and look at the Code. The alias used for the source table was in UPPER CASE. When running the same SQL with a lower case alias the query works.
To solve the problem I opened PostgreSQL under Topology/ Selected Languague and Check the Box "Objects case-sensitive"
Hope this helps someone. I spent my afternoon on this : /
Following is the full error:
ODI-1227: Task SrcSet0 (Loading) fails on the source POSTGRESSQL connection CV1.
Caused By: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "analysis"
Hint: Perhaps you meant to reference the table alias "ANALYSIS".
Position: 10
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:177)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:89)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:722)