6 Replies Latest reply on Feb 17, 2021 5:34 AM by Nico Heinze

    Bad Record Not Capturing

    Balaji R New Member
    Balaji R Feb 15, 2021 8:18 PM

      Hi,

       

      I have a table with number datatypes.  When i pass string to that number field, the data is not getting rejected by informatica.  The value '0' is inserted for that field.  I need that record to go to Informatica Error table.

       

      Can someone please help on this.  This is quite urgent.

       

      Regards,

      Balaji. R

      • 72 Views
      • Tags:
        • 1. Re: Bad Record Not Capturing
          JanLeendert Wijkhuijs Seasoned Veteran
          JanLeendert Wijkhuijs Feb 16, 2021 12:45 AM (in response to Balaji R)

          Hi

          The information you provide isn't really enough to help you.
          Can you please share some more information e.g. screenshot of the mapping?

           

          Regards,

          JanLeendert

          • 2. Re: Bad Record Not Capturing
            Nico Heinze Guru
            Nico Heinze Feb 16, 2021 4:03 AM (in response to JanLeendert Wijkhuijs)

            Understanding C programming helps in understanding the problem.

            The point is that a string is handed over to a NUMBER data type. That means that the Oracle client will invoke a certain standard C function to translate the string into a numeric value. And this C function translates as much of the string into a number as possible. Meaning that if the string doesn't start with digits, the resulting number will be zero.

            This is normal behaviour and cannot be changed.

             

            What you can do depends on what exactly you need to achieve. For example, if an ERROR() function would be ok for your mapping, you can insert an Expression in which you use something like this:

              IIF( Is_Number( in_string), To_Decimal( in_string), ERROR( in_string || ' is not numeric.'))

             

            If, however, you need this record to be rejected and listed in the .bad file for the relational target, then there's only one workaround (not a clean solution) which I see:

            Define the respective attribute as NOT NULL in the database. Then ensure that - if this string is not a number - a NULL value is written to this target. This will lead to the record being rejected and being written to the .bad file due to the NULL value.

             

            If I misunderstood what you need, please clarify with an example.

             

            Regards,

            Nico

            • 3. Re: Bad Record Not Capturing
              Balaji R New Member
              Balaji R Feb 16, 2021 8:38 AM (in response to Nico Heinze)

              Hi Nico,

               

              Thanks for your reply.  And i think your understanding is correct.  But i cant make the attributes as not null, because there is a possibility of null value to those fields.

               

              Just see below an illustration with one field

              Source(csv file)  Target(Oracle)

              abc                     Number Datatype

               

              Mapping:

              Source ----> SQ ------>Exp Trans --------> Target

               

              Initially until expression, the fields are string datatype.

              Then to fix this, the below options are tried

              1. Changed the data type in Expression to double, integer, decimal and nothing worked out

              2. Created output port and tried with conversion functions. 

              • 4. Re: Bad Record Not Capturing
                Nico Heinze Guru
                Nico Heinze Feb 16, 2021 8:49 AM (in response to Balaji R)

                The problem is that under these circumstances there is NO way to get these records into the .bad file without a little "help" from the DBAs.

                What exactly does the overall process look like? If you could tell us why exactly the .bad file is needed and what it's used for, we might be able to find a viable solution.

                 

                Regards,

                Nico

                • 5. Re: Bad Record Not Capturing
                  Balaji R New Member
                  Balaji R Feb 17, 2021 1:12 AM (in response to Balaji R)

                  Hi Nico,

                   

                  I capture all the bad records in informatica error tables and those records will be fetched to csv file using mapping.

                   

                  Regards,

                  Balaji. R

                  • 6. Re: Bad Record Not Capturing
                    Nico Heinze Guru
                    Nico Heinze Feb 17, 2021 5:34 AM (in response to Balaji R)

                    Then I have a different proposal.

                    Instead of extracting from the error log tables, simply use a Router transformation to send the "good" records to the real target(s) and the "bad" records to an "error table". Then you don't need the second mapping.

                     

                    Regards,

                    Nico