-1

I need to create a report that returns customer accounts whose account type have changed to or from this list of account_types: ('CAM','CAMADP','CAMHD','CAMHDADP','DIST','DISTADP','GAM','GAMADP','LAM','LAMADP').

Let's say I have this table today:

client account_type customer_org_ID insert_date update_date active_Y_N
Google GAM C000001 2006-05-04 09:15:34 2020-05-04 06:01:16 Y
Netflix LAM C000002 2006-05-04 09:15:34 2021-09-04 08:35:34 Y
Nordstrom DIST C000003 2006-05-04 09:15:34 2021-05-04 02:15:34 Y
Costco CAMHDADP C000004 2006-05-04 09:15:34 2021-05-04 09:36:34 Y
Boeing CAMHD C000005 2006-05-04 09:15:34 2021-05-04 12:15:45 Y
Samsung DISTADP C000006 2006-05-04 09:15:34 2020-06-15 01:10:16 Y

Let's say I come into work tomorrow and there have been changes to rows 1, 2, 3, 5, and 6 (seen in bold).

client account_type customer_org_ID insert_date update_date active_Y_N
Google CAM C000001 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Netflix DISTADP C000002 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Nordstrom GAMADP C000003 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Costco CAMHDADP C000004 2006-05-04 09:15:34 2021-05-04 09:36:34 Y
Boeing UNKWN C000005 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Samsung DISTADP C000006 2006-05-04 09:15:34 2021-11-03 12:40:41 N

I need a sql query that will return these three rows of data since there were updates made to their account_type within the list of account_types that I mentioned in the beginning.

client account_type customer_org_ID insert_date update_date active_Y_N
Google CAM C000001 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Netflix DISTADP C000002 2006-05-04 09:15:34 2021-11-03 12:40:41 Y
Nordstrom GAMADP C000003 2006-05-04 09:15:34 2021-11-03 12:40:41 Y

I am working in DB2 LUW database.

Alex Prado
  • 3
  • 2
  • For something like this you may need to use an archive table with a trigger that records the changes. – alexherm Nov 03 '21 at 22:55
  • "you would know by checking if `update_date` is a recent date" -- so why can't you check it? – mustaccio Nov 04 '21 at 12:57
  • @mustaccio Because in my database there are a lot more columns than just the 3 I listed in my question. Other columns include address, contact info, active or inactive, etc. This means that a recent update_date doesn't mean the account_type was updated. It could be the customer's contact info that changed or their address, etc. – Alex Prado Nov 04 '21 at 18:48
  • So, is this a question on how to add to this table another timestamp column and create a `before update of (account_type)` trigger updating this column, when new and old values of `account_type` are not equal and at least one of them is in the list you provided above? – Mark Barinstein Nov 04 '21 at 19:55
  • @MarkBarinstein Please see updated post... – Alex Prado Nov 07 '21 at 22:09
  • Again, you need to create a trigger on this table and add either a new timestamp column to this table or a new table. The latter is possible, if you have a unique index on the base table (please, specify it if so). Which approach would you like to implement? – Mark Barinstein Nov 08 '21 at 06:41
  • I don't have a unique index on the base table unfortunately so I'd like to implement the approach that doesn't require this..which is the trigger + the new timestamp column I believe.. @MarkBarinstein – Alex Prado Nov 09 '21 at 16:32

2 Answers2

0

DB2 offre temporal tables feature, it was introduced since the version 10.

no need to use trigger, DB2 manage this automatically

https://www.ibm.com/docs/en/db2/11.5?topic=tables-system-period-temporal

https://philipkgunning.files.wordpress.com/2013/02/traveling-through-time-with-db2-time-travel-query3apr13.pdf

mshabou
  • 524
  • 3
  • 6
0

Try this changing mytab to your real table name.

ALTER TABLE mytab ADD UPDATE_AT_DATE TIMESTAMP;

CREATE OR REPLACE TRIGGER mytab_BUR
BEFORE UPDATE OF ACCOUNT_TYPE ON mytab
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN 
(
--     O.ACCOUNT_TYPE IS DISTINCT FROM N.ACCOUNT_TYPE
--  AND
--  (
     O.ACCOUNT_TYPE IN ('CAM','CAMADP','CAMHD','CAMHDADP','DIST','DISTADP','GAM','GAMADP','LAM','LAMADP')
  OR N.ACCOUNT_TYPE IN ('CAM','CAMADP','CAMHD','CAMHDADP','DIST','DISTADP','GAM','GAMADP','LAM','LAMADP')
--  )
)
SET N.UPDATE_AT_DATE = CURRENT TIMESTAMP 
;

You may uncomment the commented out lines, if your applications may update the ACCOUNT_TYPE column with the same value as it already has, and you don't want to update the new UPDATE_AT_DATE column value in such cases.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16