6 Replies Latest reply on Apr 9, 2020 4:38 AM by Nico Heinze

    Looking Up with NULLs

    James Larsen New Member

      Hello, we are currently running Informatica 10.2, and are having trouble with one of our lookups.  Consider we have incoming data with Field A and Field B.  Our Dimension table has a PK, Field A, and Field B.  We need to do an Upsert action against the table, so are looking up if there is already a record with the same value for Field A and Field B.  The problem we're having is that we need a "NULL" on both sides of the lookup to be consider a match.  So if Field A on both sides share a value, and Field B is NULL for both the incoming record and the Dimension value, then a match should be made and the PK is retrieved.  Is there a simple way to achieve this?  Thanks!

        • 1. Re: Looking Up with NULLs
          user126898 Guru

          I would just do a NVL, ISNULL check around the fields.

           

          lookup sql on dim:  select PK, field A ,field B, NVL(field A,0), NVL(field B,0) from table_x; 

               --note the changed value i used 0 but you can use whatever will never appear in the table to represent NULL

           

          From the source you can uses an expression to check if the fields are null and default to 0 if they are.

           

           

          Now when in the lookup you will not be comparing NULL's which is always tricky.

           

          I am checking with Product Management if we have a better way but this is the method I have used before and works just fine.

           

           

          thanks,

          Scott

          • 2. Re: Looking Up with NULLs
            James Larsen New Member

            Let me preface by saying I am the Business Analyst, not the Powercenter Architect, but when they walked me through it, they were doing a lookup.  They used "Out_" parameters on the source side to change the nulls to placeholders, but on the target Dimension side, they were just mapping straight to the fields.  Is there a way to put "Out_" parameters on both sides of a lookup so we can handle nulls on both sides?  Really appreciate the help.

            • 3. Re: Looking Up with NULLs
              SaiShreya Nuguri Active Member

              Hi James,

               

              To add to Scott's message, hopefully, the following links to KB articles and product documents can be helpful on how to deal with NULL values in a lookup transformation.

               

              1. KB article: 122704  

              2.  Null Values

              3. Null Values in Lookup Condition Columns

               

              Thanks,

              Sai

              • 4. Re: Looking Up with NULLs
                user101600 Guru

                Please take a look at the following KB

                 

                534935

                113010

                123920

                • 5. Re: Looking Up with NULLs
                  James Larsen New Member

                  KB 113010 was the answer.  It indicated that NULL will match to NULL during a lookup, and we just tried it, and it worked!  Not sure why my developers didn't try that before immediately trying to get rid of the NULLs, but it worked out.  Thanks to everyone for your help!

                  • 6. Re: Looking Up with NULLs
                    Nico Heinze Guru

                    There might be a very simple answer to why they haven't tried before:

                    NULL should - as of database theory - not match anything, in particular not other NULL values. Frankly spoken I didn't know that, I didn't expect it, and I never would have tried that; I expected Informatica to behave "correctly" here, and that means (in my opinion) that NULL values should simply not match. Period.

                     

                    It's inacceptable for me personally that PowerCenter behaves that way, but well, Informatica won't change that implementation just because I'm moaning about it (no matter how good my reasoning might be). So I just have to accept that PowerCenter works in a "dirty" manner here. So I learned something new from this your question, thanks for that.

                     

                    Regards,

                    Nico