Skip to Content

HANA - FUNCTION TO GENERATE PRIMARY KEY CALLING A INSERT STATEMENT

Hello Experts,

I created a function on HCP and I would like to use it in the INSERT statement for generating the primary key.

Below is the FUNCTION code:

CREATE FUNCTION plant_key_increment_v2()
 RETURNS output NVARCHAR(64) LANGUAGE SQLSCRIPT AS
 BEGIN
 DECLARE C_PREFIX VARCHAR(64) := 'PLANT';
 DECLARE AUX VARCHAR(64);
 DECLARE I_AUX INT;
 SELECT SUBSTRING(MAX(PLANTID), 6, 3) INTO AUX FROM "SYSTEM"."MPLANT_ATTR";
 I_AUX := TO_INT(AUX);
 I_AUX := :I_AUX + 1;
 IF :I_AUX <= 9 THEN
 output := CONCAT (C_PREFIX, CONCAT('00', TO_VARCHAR(I_AUX)));
 END IF;
 IF :I_AUX > 9 AND :I_AUX <= 99 THEN
 output := CONCAT (C_PREFIX, CONCAT('0', TO_VARCHAR(I_AUX)));
 END IF;
 IF :I_AUX > 99 THEN
 output := CONCAT (C_PREFIX, TO_VARCHAR(I_AUX));
 END IF;
 END

Here I am trying to use the function in the insert statement:

INSERT INTO "SYSTEM"."MPLANT_ATTR" VALUES("SYSTEM"."PLANT_KEY_INCREMENT_V2"(),'2016-12-08T13:34:27+00:00',12,1,'','null','null',null);

And here is the error I got while executing the INSERT statement:

Is it possible to create a sequence for this purpose? How it would be done once I need to concatenate PLANT + SEQUENCE NUMBER, for instance PLANT001, PLANT002...

Many thanks,

Filipe

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 08, 2016 at 02:47 PM

    Hello Filipe,

    user-defined scalar functions cannot be called in Insert statements.

    It is also not possible to create a sequence for your requirement. A sequence can only deal with int32 values, but not with alphanumeric values.

    Options you have so far:

    • Use a trigger to set the primary key according to your requirement (with XS classic not the best option, cause triggers cannot be transported; with XS Advanced ok).
    • Create a wrapper procedure which determines the primary key using e.g. your function and inserts the data.

    One recommendation regarding your procedure logic. You should replace max(plantid) select in your coding by a coding which determines the number using a sequence. So a valid concurrency control is applied. With your coding there is possibility that several similar executed processes determine the same max plantid.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded