Calling functions for default values?

[Former Member]

Is there a way to call functions for default values on columns in the table designer? Anything I put gets put into the column as a constant.

I'd like to call date functions or a custom function I write...

Thanks, Chad

[sagarjoshi]

Why not try using Generated Column?

It may satisfy your requirements however there are certain restrictions on which functions can be used and also I dont think you can call custom defined scalar UDF functions here.

Example below:

alter table "MYPACKAGE."TABLE1" add ("DATE2" DATE null GENERATED ALWAYS AS ADD_DAYS(DATE1, DAYS))

[Former Member]

Thanks Sagar, Unfortunately that only seems to work for dates and identity columns (int) --

I'm trying to generate a guid for each row (i.e. select SYSUUID from dummy ) --

Anyone have any other ideas?

[sagarjoshi]

May be you explain your use case. Why a simple sequence not sufficient for you when generating rows?

You could check the blog

[Former Member]

The use case is pretty simple - I'm porting an application to HANA that has 100's of tables.

All of these tables have 2 columns that are used for versioning and replication. Both of these columns use a "text version" of a guid for uniqueness --

SYS_GUID() in Oracle

(replace(CONVERT([varchar](50),newid()),'-','')) in SQL Server

One column we expect to never change over the lifetime of a row and to be unique among all instances of the application.

One column we use for row versioning (i.e. we update it every time we modify the row - way cheaper than database locks).

Neither of these can use sequences --

We have these defaulted to populate with guids on SQL Server, Oracle, SQLite, etc…

I can get around the problem by dropping the "NOT NULL" requirement and building triggers to populate these guid's if they're not passed in on the insert… but I'm not looking forward to that.

Thanks -- Chad

[lbreddemann]

Hi Chad,

currently this is not possible with default values and/or generated columns.

The recommended approach here is to either generate the values during data provisioning or by using a INSERT/UPATE function layer in your db design, so that no direct inserts are executed against the table.

Using triggers is the least option that is least desirable here.

- Lars

[Former Member]

Hey Lars, thanks for the suggestions.

Unfortunately this application has about 4 million lines of code, so changing the data model is not feasible at this time.

Fortunately the schema is generated by the application, so I modified it to drop the defaults for the guids -- I'll have to use the trigger approach for now.

  Regards, Chad