on 2021 Nov 03 11:46 AM
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.
Request clarification before answering.
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:
Hope this helps.
Thanks
Dot
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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. 🙂
User | Count |
---|---|
57 | |
10 | |
8 | |
8 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.