-
1. Re: 'Where Not Exists' Issue
EC140656 Mar 13, 2019 5:03 AM (in response to inuser502305)Hi,
I would suggest the following :
1) Delete the source & add the source table again in the "Sources" in the Mapping Designer tool. This will refresh the association between the Oracle table & Informatica internally.
2) Check if the source table you have referenced in Informatica indeed points to the right table in Oracle. Some minor discrepancies are possible while mapping the source table between Oracle & Informatica in the "Source Designer" if you are new to Informatica. It is human error.
Hope it helps.
Regards
Krishna
-
2. Re: 'Where Not Exists' Issue
inuser502305 Mar 13, 2019 9:42 AM (in response to EC140656)I've tried this with no luck. I've also found that when I remove the WHERE NOT EXISTS clause that data is loaded from the source. So Informatica seems to know which table I'm referencing. Both tables are in the same schema, and the exact same query works outside of Informatica.
-
3. Re: 'Where Not Exists' Issue
Nico Heinze Mar 13, 2019 3:36 PM (in response to EC140656)In addition please double-check whether the relationa connection you're using points to the correct DB, DB schema, and table name / alias. For example, in the Environment SQL field of the connection there might be something like 'SET SCHEMA DB2SD42G' which would make the connection point to a completely different DB schema.
Regards,
Nico
-
4. Re: 'Where Not Exists' Issue
Nico Heinze Mar 14, 2019 1:06 PM (in response to Nico Heinze)Did you run this SQL query in the DB client (maybe SQL*PLUS or whatever) with the same DB user ID which is indicated in the PowerCenter connection?
If not, then my guess is that the user ID named in the connection has some synonym definition of the name XREF resp. MAME which points to a different table / schema than the one which is addressed when you run this query "manually".
At least that's my experience after >17 years with PowerCenter. PowerCenter never changes a SQL query on its own. Never. So the problem must be related to the connection resp. the DB user ID.
Regards,
Nico
-
5. Re: 'Where Not Exists' Issue
inuser502305 Mar 14, 2019 1:13 PM (in response to Nico Heinze)Running as the same DB user in both Informatica and in Oracle SQL Developer, and both tables are in the user's own schema. No synonyms are in use. I've also tried prefixing the schema name to both tables with no luck.
I've resorted to taking out the WHERE NOT EXISTS clause and instead using Lookup and Filter transformations to remove rows that already exist. I guess this way it's a little more obvious that rows are being filtered from the source anyways.
-
6. Re: 'Where Not Exists' Issue
Nico Heinze Mar 20, 2019 10:54 AM (in response to inuser502305)One last question: are we talking about PowerCenter or about the Informatica platform?
If it's PowerCenter, I strongly recommend you contact Informatica Global Customer Support, this is no normal behaviour for a relational Source Qualifier.If it's the platform, could you please try running the mapping without any optimisation? It MAY be that the built-in optimiser is playing tricks to you,
Regards,
Nico