This content has been marked as final. Show 9 replies
Could you give us some context ?
Which version of ODI? Which technology used?
If using ODI 10g or ODI 11g, please replace snpRef by odiRef.
Version: ODI 11g.1.1.5
replacing snpREF by odiREF has no effect
One solution would be to copy all the sources into the Staging Area before joining, but these tables are large and the expected output is small.
Knowledge module: IKM SQL to SQL Append
Failing Step: 3 Insert New Rows
Failing Message (paraphrased): The [Source] Schema and Table are not in the Staging Catalog
I thought the following would work:
and D_C.NAME_TYPE_CODE in ('ATTY','ATTN')
and D_C.UPDATE_DATE = (
AND D_C.NAME_TYPE_CODE IN ('ATTY','ATTN')
Where ODSHO is the LogicalSchema name, but that throws an "unknown location" error.
In general, how do you join to the most recent row in a table with a datetime/timestamp column in ODI?
It looks like you're trying to join to the source table from the staging schema, correct? Unless you have a database link, or the source schema is on the same database server, then this will not work. Even with a dblink, the performance might not be great.
You will need to join to the I$ table in the work schema. http://docs.oracle.com/cd/E14571_01/integrate.1111/e12645/odiref_reference.htm#CIAFGGCG
If you need to use the entire source table for the lookup, rather than just what's been brought into the I$ table, you'll need to perform the lookup and joins all against the source.
Could you check that the location of your join is set on the source and not on the staging area ?
The tables being joined are definitely in the Source, not the Staging Area.
I could go to the Source and create a view joining the Source tables with the subselect to get the 'current' row, but that doesn't seem very much like ODI/Sunopsis Extract/Load/Transform.
What does your flow tab look like ?
Do you have your two source tables and a join on the left, then one LKM-link towards your staging (right) ?
Or do you have two LKM-links and then a join on the staging area ?
If it looks like the second, go back to the mapping tab, click on the join and change the execution location to source.
The join was executing on the Staging Area. I changed the join to execute on the Source, but the odiRef still resolves to the Staging Area and the interface fails.
The Flow tab just shows a LKM SQL to SQL icon nested in the Target Icon.
If I change the Flow Tab Staging Area to be different from the Target, the Source Icon moves out of the Target Icon, but the LKM selection is empty.
If I select a LKM in the Source Icon the flow screen becomes blank and I see:
"Drag objects from the Navigator here to model them. Alternatively use the component palette to create new objects."
I can't think of a rational way to understand this message.
Anyway, I can't think of an interface application where GetInfo SRC_CATALOG or SCR_SCHEMA should ever/would ever resolve to the Staging Area. The staging area, if it is used, is empty at the time any join I can create in the interface is active.
In Knowledge Modules, yes, the staging area is important and all the heavy lifting takes place there, but as an interface developer I don't see/can't use that.
Right now my solution is to hard code the Catalog and Schema in the subselect.