How to use GENERATE ALWAYS AS in artifact?

[jose_at_sap]

I've created an hdbtable artifact witht the following definition:

table.schemaName = "MYSCHEMA";
table.tableType = COLUMNSTORE;
table.description = "Demo";
table.columns = [
 {name = "DriverId"; sqlType = INTEGER; nullable = false; comment = "Driver ID";},
 {name = "DriverName"; sqlType = NVARCHAR; nullable = false; length = 50; comment = "Driver Name";},
];
table.primaryKey.pkcolumns = ["DriverId"];

I want to add another column of type TIMESTAMP and want it to have a default value of CURRENT_TIMESTAMP whenever you change the row. I can't seem to figure out how to do that in my hdbtable artifact

Basically I'm looking to do the same thing as this SQL statement:

CREATE COLUMN TABLE DRIVERS (
  DriverId INTEGER NOT NULL,
  DriverName NVARCHAR(50) NOT NULL,
  last_modified TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (DriverId)
);

Can anyone please help me out? Thx!

[henrique_pinto · Accepted solution]

An alternative to GENERATED ALWAYS AS for SQL is to use the CURRENT_TIMESTAMP function as the default value.

Check Ravindra's message in this thread: http://scn.sap.com/message/13851078#13851078

You could try it in the defaultValue parameter of the table definition, but I'm not sure it'll work, tho.

I suppose XS will try to interpret "current_timestamp" as a string instead of a function name. 😕

Best,

Henrique.

[jose_at_sap]

Thank you everyone for your help.  After doing lots of digging around, I don't think it's possible to use GENERATED ALWAYS AS in the .hdbtable file.  As a workaround, I'm using a SQL script that adds the column to the table with the required column definition.

[Former Member]

Yes that's the case and it's the same for various other table artifacts like partitions. For now, you need an additional script.

John

[former_member234877]

If you need this column to show the time of change then you will have to populate it during the load using now() (or another function that sets the current time depending on which platform you are using for data load).

GENERATE ALWAYS AS columns are generated everytime you request the data (select). So it will show you timestamp of the select, not update.

Regards,

Zafer

[lbreddemann]

That's plain wrong.

GENERATED ALWAYS AS will add a persisted column to the table and will store the value whenever the input data (e.g. other columns) are changed.

Anyhow, I've no clue how to specify this in the .hdbtable file.. (yet).

- Lars

[former_member234877]

My bad, not only replied the question without thinking thoroughly, missed the question completely (hdbtable)... 

Thanks for the warning.

Regards,

Zafer