8 Replies Latest reply: Dec 10, 2012 1:34 AM by 589360 RSS

    How to update column value dynamically

    976895
      Hi All,

      I have created a simple mapping and selected insert/update on target table. In target table there is one column record_type which will hold either 'NEW' (if new record is inserting) or 'UPDATED'(if existing record is updating) value. Please let me know how can I do this using OWB PL/SQL map?

      Thanks
      Bhushan
        • 1. Re: How to update column value dynamically
          976895
          Someone please look into this.
          • 2. Re: How to update column value dynamically
            Azhar
            Hi,

            Please let us know what steps you have taken. I didnt get your question.

            Regards,
            Azhar
            • 3. Re: How to update column value dynamically
              976895
              Hi Azhar,

              Thank you for your reply.

              Let me explain in brief. Let say I have a source table TableA(col1,col2) and want to insert/update a target TableB(col1(Primary key),col2,col3). Now I want OWB map generates below merge statement.

              MERGE INTO TableB b
              USING TableA a
              ON (b.col1=a.col1)
              WHEN NOT MATCHED THEN
              INSERT(col1,col2,col3)
              VALUES(a.col1,a.col2,'*NEW*')
              WHEN MATCHED THEN
              UPDATE SET b.col2=a.col2,
              b.col3='*UPDATED*';

              Now my question is how I can pass TableB.col3 value as 'NEW" or 'UPDATED' so that OWB map generates above merge statement?
              Let me know if you need more information.
              • 4. Re: How to update column value dynamically
                589360
                Use set operators.

                You'll need 2 of them. One set operator with "Set operation" set to "Intersect" and the other set to "Minus".

                When "Intersect" is used, pass a constant "NEW" and with "Minus" pass a constant "UPDATED".
                • 5. Re: How to update column value dynamically
                  976895
                  Hi Sanjaya,

                  Thank you for your reply.

                  I like your approch but i think there is need to pass 'UPDATED' when intersect is being used and 'NEW' when minus is being used.
                  I have developed an OWB map as mentioned in below steps.
                  1. Chose source (TableA) and target (TableB) as source using table operator.
                  2. Joined TableA to TableB on col1 using left outer join using joined operator.
                  3. In expression operator I have calculated value for TableB.col3 using below logic.

                  CASE WHEN TableB.col1 IS NULL THEN 'NEW' ELSE 'UPDATED' END

                  4. Mapped columns from expression operator to target table.

                  I would like to know you comments on above approch. My OWB map has generated below merge statement.

                  MERGE INTO TableB
                  USING(SELECT MERGESQL.col1,MERGESQL.col2, CASE WHEN MERGESQL.col3 is null then 'NEW' else 'UPDATED' END col3
                  FROM (SELECT TableA.col1, TableA.col2,TableB.col1 AS col3
                  FROM TableA
                  LEFT OUTER JOIN TableB
                  ON (TableA.col1 = TableB.col1)) MERGESQL
                  ON (col1 = MERGESQL.col1)
                  WHEN NOT MATCHED THEN INSERT(col1,col2,col3) VALUES(MERGESQL.col1,MERGESQL.col2,MERGESQL.col3)
                  WHEN MATCHED THEN
                  UPDATE SET col2 = MERGESQL.col2,
                  col3 = MERGESQL.col3;
                  • 6. Re: How to update column value dynamically
                    589360
                    Hi,

                    I like your method too. It simplifies the mapping.

                    If you have time, try to measure both the methods for performance.
                    • 7. Re: How to update column value dynamically
                      976895
                      Hello Sanjaya,

                      In the same mapping I have to apply below business filter. It would be great if you provide you suggestion on the same.

                      1. I have to insert only those records from source where one of the field is changed (between source and target) or source record doesn't exists in the target.

                      Here I am intentionally not mentioning how I am trying to develop this filter so that I can see approach from people like you.
                      • 8. Re: How to update column value dynamically
                        589360
                        Hi,

                        There could be 2 scenarios when you say "one of the field".
                        1. all the fields can be changed
                        2. only a few of the fields can be changed.

                        If it's the option 2, it's an SCD type 2 implementation.

                        If it's the option 1, I'd use a combination of set operators (MINUS/ INTERSECT) for each field to determine the changed data values.
                        So, more field to check, more set operators in the mapping.

                        Regards,