Search code examples
sqldb2db2-luw

SQL Help - Detect Changes Within Specified List of Values

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.

Solution

  • 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.