Db2 – Update using select in DB2

1.

I notice in your trigger, your code says:

    INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (TA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID',  ...);

But shouldn't the VALUES clause contain vTA_ID instead?

    INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (vTA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);

However, calculating vTA_ID with your query, may not be your most efficient option.

2.

You could create a SEQUENCE object, and let it always calculate new values.

    CREATE SEQUENCE TRANS_AUDIT_SEQ AS INTEGER;

Then in your trigger,

    INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (NEXT VALUE FOR TRANS_AUDIT_SEQ, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);

3.

But what may suit you best, may be to define your ID column AS IDENTITY

CREATE TABLE LYNX.TRANS_AUDIT (
 TA_ID INTEGER  GENERATED BY DEFAULT AS IDENTITY,
    ...
 PRIMARY KEY (TA_ID)
);

Then in your trigger, you do not need to specify the value, since it will be generated for you:

    INSERT INTO LYNX.TRANS_AUDIT
        (TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (N.UNIT_ID , 'PUNIT', 'FLEET_ID',  ...);

This is most likely the best of the 3 solutions for you, unless there are other factors not mentioned.

You say you have a date table. You don't give a name so I shall refer to it as DateTable and the column in it as TheDate. You need to select from this table and LEFT OUTER JOIN to it. That way all dates in the range will have a row in the output with NULL for rows where you don't have "real" data. You can convert these to zero using COALESCE. I'll use your first posted query as an example:

Select 
    dd.date,
    dd.week,
    sum(COALESCE(p.policy_count, 0)) Policies
from DateTable dt  -- Your date table
left outer join magenta.policies p -- left join includes all dates and any polices that exist
    on p.trans_date = dt.TheDate
left outer join global.dim_date dd
    on dd.date = p.trans_date
-- use DateTable in the WHERE to get the full range of time.
Where dt.TheDate between (select week - 112 days from global.TODAY_DATE)
                  and (select week -   1 day from global.TODAY_DATE)
Group by
    dd.date,
    dd.week

Sorry, don't have a DB2 instance to hand to test it. Forgive any typos I may have introduced.