1 Reply Latest reply on Feb 13, 2020 12:27 PM by Nico Heinze

    Update Columns table -weekly in resp fields.

    vijay lakshmi New Member

      Dear all,

       

      I have a requirement to frame the below table.

       

      Table1

      --------

      Cust Id   Tier

       

      Table 2

      ---------

      Cust Id | Week1_Tier|Week2_Tier| Week3_Tier...Week54.

       

      Every week, the Table 2 must get update on the respective columns from Table1.

      If its Week1, Week1_Tier field must get updated.

      Week2 , Week2-Tier field must get updated.

       

      Can you throw some design ideas?

       

       

      Thanks,

      Vj.

        • 1. Re: Update Columns table -weekly in resp fields.
          Nico Heinze Guru

          There are basically two different approaches I see here.

           

          First you can use a SQL Transformation; the actual UPDATE statement can be constructed as a string, and so you can simply "translate" the week number of "today" (the day when the workflow runs) into the respective Week*_Tier attribute: week1 of the month to 'WEEK1_TIER', week 2 to 'WEEK2_TIER', and so on. Or as a simple expression:

            'UPDATE table2 SET WEEK' || To_Char( indate, 'W') || '_TIER = ' || current_tier || 'WHERE...'

          Then hand over this string to the SQL Transformation as e.g. a port with name QUERY; the "SQL Query" window of the SQL Transformation would simply read like this:

              ~QUERY~

           

          The second approach would use five different instances of TABLE2, all connected to the same Router transformation with five output groups (each output group corresponds to one of the five weeks of a month). This Router has the CUST_ID for the PK values of the five target instances and one WEEK_TIER input port.

          All these instances are connected to the RTR via the CUST_ID, and each instance has one of the WEEK*_TIER attributes connected to the WEEK_TIER port of this RTR.

          For example, the WEEK_TIER output port of output group 1 is connected to WEEK1_TIER of target instance 1.

          The WEEK_TIER output port of output group 2 is connected to WEEK2_TIER of target instance 2.

          And so on.

           

          Regards,

          Nico