ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL update to calculate totals on fly

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL update to calculate totals on fly

    I have an update that does a table rollover for monthly buckets along with a yearly bucket column, I am having an issue updating the yearly, I want to have the yearly bucket updated with the monthly i.e.

    update table set col1 = current, col_2 = col_1, col_3 = col_2, ...col_12=col_11, yearly_col = col_1 + col_2 + ...col_12
    where something < current_date

    the yearly_col doesn't update with the current values, suggestions on way to do this in same update statement?

    anyone point me in the right direction?

  • #2
    IMHO you have 2 choices:
    1. 2 independent Update Statements, i.e. first update COL_1 ... COL_12. After Update the totals
    2. Add an Before Update Trigger, which will automatically calculate the total before finally updating the row.

    Birgitta

    Comment


    • #3
      Thanks Birgitta, was playing around with a merge yesterday setting the current table key to the selected table key (in this case store,line,item) and it seemed to work. I think I would still like to use an update (possibly using a subselect similar)
      MERGE INTO icppsku AS s
      USING ( SELECT store,line, item,
      source_requests, source_requests1, source_requests2,source_requests3,source_requests4 ,source_requests5,source_requests6,
      source_requests7, source_requests8,source_requests9,source_requests1 0,source_requests11,source_requests12
      FROM icppsku sku
      WHERE sku.store = s.store AND sku.line = s.line AND sku.item = s.item
      ) AS curr_sku
      ON s.store = curr_sku.store AND s.line = curr_sku.line AND s.item = curr_sku.item
      WHEN MATCHED THEN
      UPDATE
      SET (source_requests1, source_requests2,source_requests3,source_requests4 ,source_requests5,source_requests6,
      source_requests7, source_requests8,source_requests9,source_requests1 0,source_requests11,source_requests12,
      source_requests_prev_12mo
      ) = (curr_sku.source_requests, curr_sku.source_requests1, curr_sku.source_requests2,curr_sku.source_requests 3,
      curr_sku.source_requests4,curr_sku.source_requests 5,curr_sku.source_requests6,curr_sku.source_reques ts7,
      curr_sku.source_requests8,curr_sku.source_requests 9,curr_sku.source_requests10,curr_sku.source_reque sts11,
      curr_sku.source_requests1+curr_sku.source_requests 2+curr_sku.source_requests3+curr_sku.source_reques ts4+
      curr_sku.source_requests5+curr_sku.source_requests 6+curr_sku.source_requests7+curr_sku.source_reques ts8+
      curr_sku.source_requests9+curr_sku.source_requests 10+curr_sku.source_requests11)
      ELSE IGNORE;

      Comment

      Working...
      X