Field-level changes using temporal tables

Hi,

you did not describe how you want the difference returned or presented in your "idea" so it is hard it image what makes sense for you. I agre with Paul Vernon that SQL would solve your problem already today. Instead of muliple views you could have only one and return the names of the columns that have changed so it would be useable for all your consumers.

Humm, looks like you can;t edit your comments in Aha!  :-(   I should have said "Also, I can't see why you can't do what you ask with a simple view.

Thanks for your detailed response.

I'm not asking for multiple system history tables for a base table.

We're trying to enable self-serve data provisioning for multiple data consumers, who each may need changes to different data elements, WITHOUT the need to build multiple objects/hard-code the fields for each consumer.

Let me try explain better:

Say we have a Employee table that tracks employee history, as a bitemporal table, with the following fields:
EmployeeID
System temporal columns
Application temporal columns
FirstName
LastName
EmailAddress
BusinessPhone
HomePhone
OfficeLocation
OfficeDesk
EmployeeStatus
HireDate
TerminationDate

For this data, say Consumer A is only interested in changes to FirstName, LastName, and EmailAddress, whereas Consumer B is interested in changes to FirstName, LastName, BusinessPhone, and HomePhone.

We don't want to create a separate view per consumer since this object in reality has over 100 fields and over 30-40 consumers, each with different subset of columns that they're looking for changes in.

Since Temporal tables have change information at a row-level, we were trying to see if there's a way to utilize this information to know what has changed at a field-level, in a dynamic manner, for consumers to look for changes to the fields that they're interested in.

I'm not sure that this is a very common requirement. Are you asking to be able to have more than 1 SYSTEM HISTORY TABLE for a given base table, and with a user definable sub-set of columns triggering historical capture for each history table?  Again, does not sound very widely applicable. Have you got some more back-ground into this request? It is a mult-tennency thing? 

Also, I can't see why you can do what you ask with a simple view.  It might not be a performant at query time as separate history tables, but might well be sufficient for your needs.  See below for an example

Note that I use a join on SYS_START below (i.e. I ignore and TRANSACTION START ID complexity), but you could use LAG() and sort ordering instead...  (I also ignore NULL changes, but again you could add that)

CREATE TABLE H
(
 col1 int not null, col2 int, col3 int, col4 int, col5 int, col6 int,
 sys_start    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN,
 sys_end      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,
 ts_id        TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
 PERIOD SYSTEM_TIME (sys_start, sys_end)
)
organize by row;
CREATE TABLE HH LIKE H organize by row;
ALTER TABLE H ADD VERSIONING USE HISTORY TABLE HH;
INSERT INTO H VALUES (1,2,3,4,5,6);
UPDATE H SET COL4 = COL4 + 1;
UPDATE H SET COL4 = COL4 + 1, COL5 = 8;

CREATE VIEW SYSTEM_A_CHANGES AS
SELECT H.COL1, H2.COL1 AS PREV_COL1
,      H.COL2, H2.COL2 AS PREV_COL2
,      H.COL4, H2.COL4 AS PREV_COL4
FROM H  FOR SYSTEM_TIME BETWEEN '0001-01-01' AND '9999-01-01'
JOIN
     H FOR SYSTEM_TIME BETWEEN '0001-01-01' AND '9999-01-01'  AS H2
ON
    H.sys_start = H2.sys_end
WHERE
    H.COL1 <> H2.COL1
OR  H.COL2 <> H2.COL2
OR  H.COL4 <> H2.COL4
;