6 Replies Latest reply on Mar 20, 2019 10:54 AM by Nico Heinze

    'Where Not Exists' Issue

    inuser502305 New Member
    inuser502305 Mar 13, 2019 2:16 AM

      I'm trying to only load rows from an Oracle DB source that don't already exist in the target using an SQL Override in the Source Qualifier.  When I run this query in Oracle SQL Developer I get data.  When I put it in Informatica, however, 0 rows get read from the source table. 

       

      SELECT 
XREF.U_ID AS U_ID,
XREF.M_ID AS M_ID
FROM xref
WHERE NOT EXISTS (
SELECT * FROM mame
WHERE
mame.e_id = XREF.U_ID
mame.ef_id = XREF.M_ID
)

       

      I've checked the Session Log and there are no errors and it's using the correct SQL statement; it's just getting 0 records.

      • 74 Views
      • Categories: Troubleshooting
      • Tags:
        • 1. Re: 'Where Not Exists' Issue
          EC140656 Seasoned Veteran
          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 New Member
            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 Guru
              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 Guru
                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 New Member
                  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 Guru
                    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