1 2 3 Previous Next 35 Replies Latest reply on Dec 27, 2019 7:01 PM by Jay Ram

    IIF condition for insert and update

    Jay Ram New Member

      Hi - I have a mapping which is reading the data from a flat file and store it in target oracle database by referring 3 tables for joining condition and to append the values based on the matching conditions from those reference tables. After doing trimming, lookup and validation we are having one expression transformation which contains this condition connecting to router transformation with 2 groups -insert and update.

       

      IIF(ISNULL(ID),'INSERT',IIF (TGT_FLAG <> SRC_FLAG OR TGT_STATUS
      <>SRC_STATUS OR TGT_TERM_DATE
      <> SRC_TERM_DATE AND TGT_FLAG='N','UPDATE','REJECT'))

       

       

      Informatica 10.1.0  Oracle 11g

       

      Here the issue is that it's getting inserted with duplicate records without updating the existing records. Here i'd like to add more conditions incase of insert (comparing 2 additional columns which is not equal and adding more condition for update as well by comparing  2 more columns which should be equal.

       

      Could you please help me on this?

        • 1. Re: IIF condition for insert and update
          Nico Heinze Guru

          I could write down some IIF chain here, but what would this be good for as long as I am not sure I understand the conditions you need to meet.

          It would be safer if you could please describe in plain text the conditions for a source record to be cleanly identified as "new", "to be updated", or "to be rejected". Then it's much easier to re-word these conditions as an IIF chain or (which usually is far more readable) a fitting DECODE()  statement like this:

           

          Decode( TRUE,

            IsNull( ID), 'INSERT',

            TGT_FLAG <> SRC_FLAG OR

                TGT_STATUS <>SRC_STATUS OR

                TGT_TERM_DATE <> SRC_TERM_DATE AND

                TGT_FLAG='N',

              'UPDATE',

              'REJECT')

           

          Not to forget that this condition contains an often deadly error.

          Whoever has written this condition didn't pay attention to a very basic rule of almost all programming languages, namely "never mix AND and OR in one condition". The second condition above contains three terms OR'ed together and a fourth one being AND'ed to.... yep, to what? Which operator binds how? In what priority / hierarchy?

          This is a very typical bad error, and that's one more reason why I have written above, "please write in plain text".

           

          Regards,

          Nico

          • 2. Re: IIF condition for insert and update
            Jay Ram New Member

            Hi Nico - Here are the target definition columns -

            TGT_ID(PK)

            TGT_POL_ID(PK)

            TGT_PC_ID

            TGT_POL_NUM

            TGT_PC_CODE

            TGT_SRV_MIL

            TGT_COUP_VAL

            TGT_AVAI_FLAG

            TGT_SRV_TYPE

            TGT_STATUS

            TGT_TERM_DATE

            TGT_CLM_NUM

            TGT_CREATED_DATE

            TGT_CREATED_BY

            TGT_LAST_UDPATED_DATE

            TGT_LAST_UDPATED_BY

             

            As i mentioned before we are getting source feed as flat file and only these column values are supplied by data feed.-TGT_POL_NUM,TGT_PC_CODE,TGT_SRV_MIL,TGT_COUP_VAL,TGT_AVAI_FLAG,TGT_SRV_TYPE,TGT_STATUS,TGT_TERM_DATE.Rest of the values are passed by the reference tables which are joined by using lookup transformations.

             

            for the first time the data feed contains all values except -TGT_STATUS,TGT_TERM_DATE and the second time for the updation of the existing record it has TGT_POL_NUM,TGT_PC_CODE,TGT_STATUS and TGT_TERM_DATE.

            Now the issue is with the value TGT_PC_ID which are fetching from another table by the comparing with the column value  TGT_PC_CODE from source feed. Some times it has duplicate values for a particular TGT_PC_CODE in the reference table due to which we are getting duplicate records in the target as well.

             

            Even though it has 2 different TGT_PC_CODE ,i'd like to treat them as a single record in my target table and for the second time it should get updated with new TGT_PC_CODE in the existing record instead of inserting as a new one.

             

            Could you please help me in deriving the insert and update conditions here.

            • 3. Re: IIF condition for insert and update
              Nico Heinze Guru

              Before I post my suggestions, I would like to understand whether the lookup for the TGT_PC_CODE is a "normal" Lookup which returns only one record or whether this is a so-called Multiple Match LKP which returns all fitting records. The second one would explain why you get duplicate records in the target, but then the question is why this LKP has been set up to return all matching records.

               

              In short, there are several approaches to remedy such a situation. But without understanding your business requirements and technical needs I can only guess what might fit in here, so I can - right now - only list two suggestions together with a few advantages and disadvantages.

               

              First: change the LKP to return only one of the matching records. Now the danger is that - depending on the setup of the LKP - it may return the wrong details for such a "duplicate" record. On the other hand mappings usually can be built far easier with a "nornal" passive LKP (one returning only one matching record).

               

              Second you could set up a new table which is filled from the LKP table, but in such a way that no duplicate records are written to that table, only the "right" details per TGT_PC_CODE. This would enable you to implement whatever logic is needed to cater for "correct" details per value of TGT_PC_CODE, and you don't have to change your current working mapping each time this logic needs to be adapted; you only have to change the LKP from Multiple Match to any of the other four possible strategies, and that's it.

               

              Again, how can it happen that this LKP contains "duplicate" records, and which of these "duplicates" should be used under what conditions in your mapping?

               

              Regards,

              Nico

              • 4. Re: IIF condition for insert and update
                Jay Ram New Member

                Thanks for your reply and apologize for the delayed response ,Nico. Here the LKP TGT_PC_CODE would return only one record.

                 

                The root cause of this issue is due to the master reference table which is maintaining one more than record for the column value-TGT_PC_ID in the target table where we are getting the values of TGT_PC_ID ,TGT_PC_CODE from that master reference table.

                 

                So whenever there is any updation happens if the source reference table has more than one entry it will consider it as a new record and inserting as a new row. In our mapping logic we have one expression transformation before the router which has 2 groups -insert and update.

                 

                In the expression transformation, we have set a flag  for insert and update by declaring some expression conditions over there.Here i'd like to update in the same record  if the target table has same

                TGT_POL_NUM,TGT_PC_CODE column values  if it has  value which is not in target then it should be inserted.

                 

                Also it should get updated with new TGT_PC_ID in the existing record.

                 

                I tried with this one in expression which has flag for update and insert before router

                IIF((SRC_POL_NUM  != TGT_POL_NUM AND SRC_PC_CODE != TGT_PC_CODE),'INSERT',IIF (TGT_POL_NUM = SRC_POL_NUM AND TGT_PC_CODE = SRC_PC_CODE,'UPDATE'.'REJECT'))

                 

                But it keeps on running for longer time without updating anything

                 

                Could you please help me in getting the right expression for insert and update flag ?

                 

                 

                • 5. Re: IIF condition for insert and update
                  Nico Heinze Guru

                  i'm trying to do exactly that all the time.

                  First: what about the mixed use of OR and AND operators in your original IIF() term above?

                  Please re-read my intiial response. Using OR and AND operators in the same IIF condition is almost always an error, in particular because you can never be sure in what order the individual conditions are tested.

                  Please, before trying anything else, make sure that you change that term using parentheses "()"; make sure that it's 100% clear which single conditions are combined by OR and which ones are combined by AND. Mixing up these operators is deadly.

                   

                  Or did you clean up your IIF condition in the meantime?

                   

                  Regards,

                  Nico

                  • 6. Re: IIF condition for insert and update
                    Jay Ram New Member

                    Hi Nico - I have cleaned up the IIF condition and tried to use this one-

                    IIF((SRC_POL_NUM  != TGT_POL_NUM AND SRC_PC_CODE != TGT_PC_CODE),'INSERT',IIF (TGT_POL_NUM = SRC_POL_NUM AND TGT_PC_CODE = SRC_PC_CODE,'UPDATE'.'REJECT'))

                     

                    which means if Pol_num and PC_CODE from source and target are not equal then it should insert if it's equal then it should update. But it's not working.

                     

                    Also when we are using update override option in target condition if we use non primary key columns in where clause ,will it work?

                     

                    Really this column TGT_PC_CODE makes all the issues since it has two unique values in the master reference table it takes as a new record and tried to insert it.

                     

                    I want to ignore in condition checking level so that we can easily update with the existing records.

                     

                     

                    • 7. Re: IIF condition for insert and update
                      Jay Ram New Member

                      More insight on the requirement.Here are the 2 sample insert records

                       

                      Insert into TGT  values (9508679124092014,9502502624092014,17244142215122009,'C00020112977','P9N55',55000,30,'Y','8','0',null,null,to_timestamp('24-SEP-14','DD-MON-RR HH.MI.SSXFF AM'),'BATCH',to_timestamp('24-SEP-14','DD-MON-RR HH.MI.SSXFF AM'),'BATCH');
                      Insert into TGT  values (1606833201112018,9502502624092014,38785767826042016,'C00020112977','P9N55',0,0,'Y','0','A',to_timestamp('15-DEC-19','DD-MON-RR HH.MI.SSXFF AM'),null,to_timestamp('01-NOV-18','DD-MON-RR HH.MI.SSXFF AM'),'BATCH',to_timestamp('26-NOV-19','DD-MON-RR HH.MI.SSXFF AM'),'BATCH');

                       

                      Our requirement is for the combined column values of TGT_POL_NUM +TGT_PC_CODE should have unique record since the column TGT_PC_ID has two diff IDS in the reference table it considering as a new record and inserting as a new one. But in our target table(TGT) the TGT_POL_NUM +TGT_PC_CODE values should have unique value and should get updated in the existing record.Here in the above eg both the column has same values.

                       

                      Also ,here is my query to get the duplicate records in my table

                       

                       

                      select count(*),TGT_POL_NUM,TGT_PC_CODE from TGT group by TGT_POL_NUM,TGT_PC_CODE having count(*) >1;

                       

                       

                      Here in the above insert stat values i'd  like to update the records for the 3rd column (TGT_PC_ID)=17244412215122009(older one) based on the next record with value 38785767826042016(newer one). Here are the columns i'd like to update in the 1st record -TGT_PC_ID , TGT_STATUS,TGT_TERM_DATE,TGT_LAST_UDPATED_DATE,TGT_LAST_UDPATED_B

                      • 8. Re: IIF condition for insert and update
                        Nico Heinze Guru

                        Why do you use AND in the first condition? Shouldn't the current record be considered "new" when any of these two PK attributes is different?

                         

                        Also, the whole chain doesn't make too much sense to me. OK, if any of the two values differs, the record is new, so far so good. But why would you want to reject a record?

                         

                        I don't recall whether I have written that before, so please grant me your pardon if I repeat myself here.

                        It would help if you could explain in plain text (at best in "business language") what exactly your mapping is supposed to do. Maybe then I can finally understand what you mean by this table with "duplicate" records, that's still puzzling me, to be honest. But that might well be because I am trying to respond to this thread at 11 p.m.

                         

                        Regards,

                        Nico

                        • 9. Re: IIF condition for insert and update
                          Jay Ram New Member

                          I have posted (the previous one)the  detailed explanation with sample records.If not, can we set a separate flag(in 2 different port) for insert and update in the expression transformation and link the same to the router ?

                          Also here is the stored proc which i designed to delete and update the existing records for an one time fix and that's

                           

                          Moreover when we are using update override option in target definition if we use non primary key columns in where clause ,will the condition work?

                           

                          MERGE INTO  tgt  dst
                          USING (
                                    SELECT  *
                                    FROM    tgt  n              -- N is for Newer
                                    WHERE  EXISTS
                                            (
                                                SELECT  1
                                                FROM    tgt  o  -- O is for Older
                                                WHERE  o.tgt_pol_num  = n.tgt_pol_num
                                                AND    o.tgt_pc_code  = n.tgt_pc_code
                                                AND    o.tgt_pc_id    < n.tgt_pc_id
                                            )
                                )          src
                          ON  (    dst.tgt_pol_num  = src.tgt_pol_num
                              AND  dst.tgt_pc_code  = src.tgt_pc_code
                              )
                          WHEN MATCHED THEN UPDATE
                          SET    dst.tgt_pc_id              = src.tgt_pc_id
                          ,      dst.tgt_status            = src.tgt_status
                          ,      dst.tgt_term_date          = src.tgt_term_date
                          ,      dst.tgt_last_updated_date  = src.tgt_last_updated_date
                          ,      dst.tgt_last_updated_by    = src.tgt_last_updated_by
                          DELETE WHERE  dst.tgt_id      = src.tgt_id
                                AND    dst.tgt_pol_id  = src.tgt_pol_id
                          ;

                          Please let me know if you need more information on this.

                          • 10. Re: IIF condition for insert and update
                            Nico Heinze Guru

                            Sorry, obviously I still misunderstand the update / delete condition. Again, could you please post a plain-text business-oriented description of the condition when a record must be updated / deleted / inserted?

                             

                            As to the Target Update Override, this is meant to be used in cases where the PK can't be used (for whatever reasons). You only have to follow one rule for the Target Update Override:

                            Write the whole statement into one single line. No line breaks. White space is ok, and as the UPDATE statement probably will be too long to fit into the editor window it will be wrapped around by the editor window itself; this is ok and will work fine, but never ever hit the Enter key while keying in the UPDATE statement; PowerCenter will only read the first line entered in this window, meaning the UPDATE statement will be incomplete if you enter it in two or more lines.

                             

                            Regards,

                            Nico

                            • 11. Re: IIF condition for insert and update
                              Jay Ram New Member

                              Hi Nico -  If the target table has same TGT_POL_NUM,TGT_PC_CODE(both are not primary key col) column values when comparing with source feed then i would like to update , if it's not in target there t then it should be inserted.

                               

                              Which means there should be unique value for the combined column values of TGT_POL_NUM,TGT_PC_CODE in target and no duplicate records and if there are any changes then it should get updated ,if new value record comes then it should get inserted.

                              • 12. Re: IIF condition for insert and update
                                Nico Heinze Guru

                                Ah, ok, now only I saw your post from Dec 6th. I have simply overlooked it until the other second.

                                 

                                OK, let's go with a fresh approach.

                                 

                                First I would simply try to find out whether a given input record already exists. This can be done using a simple LKP on the target table (though I don't like doing so, but that's a different story, so I put aside this point for now). The LKP simply checks whether the combination of TGT_POL_NUM and TGT_PC_CODE from your input record already exists in the target table or not. If so, you have to set the Update Strategy value to DD_UPDATE; if it does not yet exist, set the Update Strategy value to DD_INSERT.

                                Let's suppose you configure the LKP to return the TGT_POL_NUM and the TGT_PC_CODE for any given input record. Then you can configure the Update Strategy condition, like this (assuming that these two attributes can never be NULL in the target table):

                                IIF( IsNull( lkp_TGT_POL_NUM) OR IsNull( lkp_TGT_PC_CODE), DD_INSERT, DD_UPDATE)

                                 

                                Do you really need to delete or reject records as well?

                                If so, how do you identify whether to delete / reject an input record from the target?

                                 

                                Regards,

                                Nico

                                • 13. Re: IIF condition for insert and update
                                  Jay Ram New Member

                                  Ok Nico,lets give a try and let you know the result.

                                   

                                  Again, i have created a stored proc(posted in above response) to delete and update the existing records for one time purpose but this ETL job needs to ensure that no duplicate records get inserted going forward.

                                   

                                  But i would be be nice if we would have included that option also in the mapping.

                                   

                                  MERGE INTO  tgt  dst  
                                  USING (  
                                  SELECT  *  
                                  FROM    tgt  n              -- N is for Newer
                                  WHERE  EXISTS  
                                                    (  
                                  SELECT  1  
                                  FROM    tgt  o  -- O is for Older
                                  WHERE  o.tgt_pol_num  = n.tgt_pol_num  
                                  AND    o.tgt_pc_code  = n.tgt_pc_code  
                                  AND    o.tgt_pc_id    < n.tgt_pc_id  
                                                    )  
                                        )          src  
                                  ON  (    dst.tgt_pol_num  = src.tgt_pol_num  
                                  AND  dst.tgt_pc_code  = src.tgt_pc_code  
                                      )  
                                  WHEN MATCHED THENUPDATE
                                  SET    dst.tgt_pc_id              = src.tgt_pc_id  
                                  ,      dst.tgt_status            = src.tgt_status  
                                  ,      dst.tgt_term_date          = src.tgt_term_date  
                                  ,      dst.tgt_last_updated_date  = src.tgt_last_updated_date  
                                  ,      dst.tgt_last_updated_by    = src.tgt_last_updated_by  
                                  DELETEWHERE  dst.tgt_id      = src.tgt_id  
                                  AND    dst.tgt_pol_id  = src.tgt_pol_id  
                                  ; 
                                  
                                  

                                   

                                  • 14. Re: IIF condition for insert and update
                                    Nico Heinze Guru

                                    You use the Update Strategy transformation to mark a source record to be either inserted into the target table or to be updated in the same table.

                                    Make sure that the session property Treat Source Rows As is set to Data Driven (otherwise the settings in the Update Strategy transformations are ignored, that's no joke) and that you key in the correct UPDATE statement in the Target Update Override property of the target instance.

                                    Furthermore make sure that in the target properties for that target table you check the Insert check box and the correct Update box (there are three of them, Update As Update should be correct for your case).

                                     

                                    If that was too much information, let us know, then I'll explain it all in more detail.

                                    And please accept my apologies for not having seen your post with the sample data. No idea why I've missed it for five days. Sigh.

                                     

                                    Regards,

                                    Nico

                                    1 2 3 Previous Next