1 Reply Latest reply: Sep 6, 2017 2:36 AM by Marcus Sommer RSS

    Using a script to update a table

    Stephen Naude

      Hi

       

      I have 3 tables:

       

      table A contains the Call data for refunds,main columns are:

      Call_ID, ID_Number

       

      Table B contains transaction data where a CALL_ID was written into the data, Main Columns are:

      Acc_Number, Call_ID, ID_Number

       

      Table C contains transaction data where CALL_ID was NOT written to the Data, Main Columns are:

      Acc_Number, ID_Number

       

      Table C will inherently have duplicated data that is already in Table B.

       

      My thinking was to use Table A to Update table C and bring the CALL_ID in, and then load it back to Table B (Concatenated). End Result is only have Table A and Table B, Table C gone.

       

      Problem is that i have no idea how to pull this off.

       

      Can anyone assist please.

        • Re: Using a script to update a table
          Marcus Sommer

          I would probably do the following:

           

          MapAtoC:

          mapping load ID_Number, Call_ID from TableA;

           

          TableBandC:

          load

               Acc_Number, Call_ID, ID_Number,

               Acc_Number & '|' & Call_ID& '|' & ID_Number as Key

          from TableB;

               concatenate

          load

               Acc_Number, ID_Number,

               applymap('MapAtoC', ID_Number, '#NV') as ID_Number

          from TableC

          where not exists(Key, Acc_Number & '|' & Call_ID& '|' & applymap('MapAtoC', ID_Number, '#NV');

             

          and removing the tables A + C. If you want to keep A you will need a combined key to avoid synthetic key - also you might want to handle possible duplicates in a different way - its just a suggestion how you could approach to this topic.

           

          - Marcus