I am using IKM Oracle Slowly Changing Dimension to implement SCD type 2.
I have a sequence marked as Surrogate Key, A Natural Key, 4 columns with behaviour set as 'Add Row On Change', 2 columns as ' Overwrite on change', 1 Start_timestamp, end timestamp and flag column in the datastore.
I have the flow control turned on.
When I execute the interface, I get the following error in step Update Existing Rows
ORA-01747: invalid user.table.column, table.column, or column specification"
I have gone through the ODI Experts blog and cross-checked that I am following everything. I have also gone through the threads on OTN.
Please provide me with a solution. It is urgent.
That message means the syntax of the SQL ODI has generated and sent to the database is incorrect, usually means something has been mis-configured in your ODI Model, Interface etc.
Go into Operator, copy the SQL from the step that is failing , run it in the target DB as the same user configured for that data server in Topology, it will give you the line number which (at least) which will be a start.
Also paste up the generated SQL here,
What I would do next is have a look at the Knowledge module step, find the specific API call generating the incorrect syntax and see what its looking for in the ODI metadata, e.g
Consult the Substitution API reference documentation - its well written up.
<%=odiRef.getColList("", "Y.[COL_NAME]=X.[COL_NAME]", "\n\tand\t", "", "PK")%> for example is looking for primary key columns to be defined in the interface. Usually you can debug it from there pretty easily.