cancel
Showing results for 
Search instead for 
Did you mean: 

System-versioned tables in SAP HANA WEB IDE

larsmillgaard
Discoverer
0 Kudos

Hi,

Is it possible to define a system-versioned table in SAP HANA WEB IDE as a design time object. The SQL example below is functional - however not implemented in WEB IDE.

CREATE COLUMN TABLE account_history (
  account_id INT,
  account_owner_id NVARCHAR(10),
  account_balance DOUBLE,
  valid_from timestamp NOT NULL,
  valid_to timestamp NOT NULL
 );

CREATE COLUMN TABLE account (
  account_id INT PRIMARY KEY,
  account_owner_id NVARCHAR(10),
  account_balance DOUBLE,
  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 account_history;

Best regards,

Lars

amish1980_95
Participant
0 Kudos

Hello Lars,

I hope you got your answer.

I used the syntax given in your question for creatin the system versioned tables in sap data Intelligence.

Do you have nay idea, how do we auto merge history and main tables?

I have used:

create table "schema"."DLTransInfo_SV2"("AssetClassCode" tinyint NOT NULL,

"TransactionId" bigint NOT NULL,"CurrCode" varchar(5),"DealAttCode" char(1),"SubStatusCode" varchar(3),

"SysValidFrom" TIMESTAMP GENERATED ALWAYS AS ROW START NOT NULL,

"SysValidTo" Timestamp GENERATED ALWAYS AS ROW END NOT null, primary key("AssetClassCode","TransactionId"),

PERIOD FOR SYSTEM_TIME("SysValidFrom", "SysValidTo"))

UNLOAD PRIORITY 5 AUTO MERGE with system versioning history table "schema"."DLTransInfo_SV2_History" NOT VALIDATED;

It does not create the main table with this query but with the below one, it does but i don't see data in history table:

create table "schema"."DLTransInfo_SV2"("AssetClassCode" tinyint NOT NULL,

"TransactionId" bigint NOT NULL,"CurrCode" varchar(5),"DealAttCode" char(1),"SubStatusCode" varchar(3),

"SysValidFrom" TIMESTAMP GENERATED ALWAYS AS ROW START NOT NULL,

"SysValidTo" Timestamp GENERATED ALWAYS AS ROW END NOT null, primary key("AssetClassCode","TransactionId"),

PERIOD FOR SYSTEM_TIME("SysValidFrom", "SysValidTo"))

with system versioning history table "schema"."DLTransInfo_SV2_History" NOT VALIDATED UNLOAD PRIORITY 5 AUTO MERGE;

Please advise.

Thanks.

amish1980_95
Participant
0 Kudos

I got my answer 🙂 thanks.

Accepted Solutions (0)

Answers (1)

Answers (1)

pfefferf
Active Contributor
0 Kudos

Both tables have to be defined via a .hdbtable artifact. The system versioning relation between the tables has to be defined via a .hdbsystemversioning artifact.

larsmillgaard
Discoverer
0 Kudos

Thanks for the reply. Did not consider .hdbtable since my understanding is that .hdbcds is the recommended way forward. Can you share an example of the required definitions in the .hdbtable and .hdbsystemversioning files?

Did you try this scenario using CDS artefacts?

Best regards,

Lars

pfefferf
Active Contributor
0 Kudos

Examples are already provided in the documentation.

No, I didn't tried that with CDS entities.

larsmillgaard
Discoverer
0 Kudos

Thank you, Florian. I actually found an example provided by Thomas Jung ... for some reason it did not show in a google search --> SystemVersionedTest