cancel
Showing results for 
Search instead for 
Did you mean: 

System versioned tables in SAP Data Intelligence.

amish1980_95
Participant
0 Kudos
277

Hello All,

I am trying to create System versioned tables in SAP Data Intelligence.

Appreciate if any one can anyone provide some insights?

Thanks,

Indu.

Accepted Solutions (0)

Answers (1)

Answers (1)

DotEiserman
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Indu,

Here is a sample piece of code that you can apply to your scenario.

-- first create the history table

CREATE COLUMN TABLE cats_history (
  Cat NVARCHAR(10), 
  DateOfBirth DATE, 
  Breed NVARCHAR(30),
  valid_from timestamp NOT NULL,
  valid_to timestamp NOT NULL
 );
-- then create the main table that references the history table
CREATE COLUMN TABLE cats (
  Cat NVARCHAR(10) PRIMARY KEY, 
  DateOfBirth DATE, 
  Breed NVARCHAR(30),
  valid_from TIMESTAMP NOT NULL GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP NOT NULL GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
 )
 WITH SYSTEM VERSIONING HISTORY TABLE cats_history;

In my table Cat is the primary key. I then inserted cats a few seconds apart (note that I don't specify the valid_from and valid_to, the system does that for me):

INSERT INTO cats VALUES('Diva','2009-01-01', 'Maine Coon');

INSERT INTO cats VALUES('Katy','2013-05-01', 'Maine Coon');

INSERT INTO cats VALUES('Mousie','2021-05-01', 'Maine Coon');

You can then query the main table at a particular point in time with the following statement:

SELECT * FROM cats FOR SYSTEM_TIME AS OF '2021-11-11 1:17:40.2070628 PM'

It's nicely explained at the following documentation link:

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.05/en-US/91302b26f62c4433bbc58e0a95...

Hope this helps.

Thanks

Dot

amish1980_95
Participant

Hello Dot,

Thank you very much 🙂

I really appreciate your detailed answer 🙂

There is one bit more which I have added to my query is unloading the data from main to history table by setting the load priority. 🙂