8 Replies Latest reply on Jul 3, 2021 11:50 AM by Nico Heinze

    Delete records using PowerCenter

    asagpariya_ciber Active Member

      Hi All,

       

      I have simple mapping which extracts data from source and deletes in target. It has composite primary key (composed of 10 columns). This mapping uses only those required 10 columns.

       

      I have not used Update Strategy transformation, rather used session level properties to delete records.

      The mapping works fine but if one of these 10 columns contains null value, it does not delete it.

       

      What I can do is to replace null value with other while extracting data from source but cannot do same in target. Can someone help me finding work-around?

        • 1. Re: Delete records using PowerCenter
          Rajesh Thiagarajan Seasoned Veteran

          It would be helpful if you can let us know the PC version along with the target database and the connection you are using. This will help validate and see if there are any workarounds available.

          • 2. Re: Delete records using PowerCenter
            asagpariya_ciber Active Member

            Hi,

             

            PowerCenter Version: 10.4.1

            Src/Tgt database: MS SQL Server 12

            Connection: SQL Server connection

            • 3. Re: Delete records using PowerCenter
              Alexandru Stan New Member

              So, you are having a table, 10 Columns, these columns are all parts of your composite primary key. In this case, even with SQL Server, these columns shall be not null. Please check the ddl of the table.

               

              https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms179610(v=sql.110)

               

              All columns defined within a primary key constraint must be defined as not null. If nullability is not specified, all columns participating in a primary key constraint have their nullability set to not null.

               

               

               

              Alexandru Stan

              • 4. Re: Delete records using PowerCenter
                Nico Heinze Guru

                To add to Alexandru's response, if the source table contains NULL values for the PK columns, then it's simply impossible to use these records in a "normal" way to delete the matching from the target table (because NULL does not match anything).

                 

                You have to find out (with the help of the application designers and/or DBAs) what shall be done when there are NULL values in the source table. Shall all other not-NULL attributes to delete the target records matching those other attributes? Something else?

                This is simply nothing that can be done in any purely "technical" way (at least not without quite some nasty and big effort). Your peers have to provide you with more information about the task you shall implement.

                 

                For example, it MAY be that the NULL values in the source database are wrong and should be "real" values.

                 

                It MAY also be that you are expected to delete based on the non-NULL attributes. A solution can be constructed in this case (without the need to create 1,023 instances of the target table), but first we need to know whether that's really what you are expected to implement.

                 

                Regards,

                Nico

                • 5. Re: Delete records using PowerCenter
                  Arya Nymeria Active Member

                  Agree with Nico

                  • 6. Re: Delete records using PowerCenter
                    David Lopez Cruz Guru

                    In SQL Server you would need to use IS NULL condition to test for a NULL value.

                     

                    Regards,

                    David

                    • 7. Re: Delete records using PowerCenter
                      Akilan Chandrasekaran Active Member

                      I'm not able to think of any workaround from Informatica PC end but from the DB table if it is possible to have the default value (Let say 'NA') that would be one workaround but again you can validate magnitude of this simple change.

                      • 8. Re: Delete records using PowerCenter
                        Nico Heinze Guru

                        There is one workaround, I already gave a hint towards it in an earlier response.

                        What can be done is to build a SQL statement to remove records, and the WHERE part would be built depending on which values from the "control table" contain NULL values.

                        So, in an EXP I would create a string output port of let's say 4000 characters; the text would look like this:

                            'DELETE FROM ' || tablename || ' WHERE ' ||

                          IIF( IsNull( col1), 'col1 IS NULL', 'col1 = ' || To_Char( col1)) || ' AND ' ||

                          IIF( IsNull( col2), 'col2 IS NULL', 'col2 = ' || To_Char( col2)) || ' AND ' ||

                        ...

                        and so on until

                          IIF( IsNull( col10), 'col10 IS NULL', 'col10 = ' || To_Char( col10))

                         

                        Then forward this string to a SQL Transformation of the correct database type which executes this whole string as the SQL command.

                         

                        Granted, this example is a little bit simplified, primarily because I assumed that all 10 columns are of some numeric data type, but expanding this example above to cope for other data types should not be too difficult.

                         

                        Regards,

                        Nico