CODEWITHSUNDEEP
sqlsequencehana
SG Tech Edge
SG Tech Edge

Reputation: 507

HANA: How to properly "CREATE SEQUENCE START WITH" with a dynamic start value?

I want to create a SQL statement for a sequence that will dynamically set the starting value.

The following SQL statement runs into an error.

CREATE SEQUENCE id_seq START WITH (SELECT MAX(identificationnumber)+1 FROM newsletter_status);

Could not execute 'CREATE SEQUENCE id_seq START WITH SELECT MAX(identificationnumber) FROM newsletter_status'Error: (dberror) [257]: sql syntax error: incorrect syntax near "SELECT": line 1 col 41 (at pos 42)

I should mention that SELECT MAX(identificationnumber) FROM newsletter_status works properly and returns a number.

Trying to extract the value first into a variable does not help too.

DO
BEGIN
    DECLARE identificationnumber_max BIGINT;
    SELECT MAX(identificationnumber) INTO identificationnumber_max FROM newsletter_status;
    CREATE SEQUENCE id_seq START WITH :identificationnumber_max + 1;
END;

Could not execute 'DO BEGIN DECLARE identificationnumber_max BIGINT; SELECT MAX(identificationnumber) INTO ...'Error: (dberror) [257]: sql syntax error: incorrect syntax near ":identificationnumber_max": line 5 col 38 (at pos 184)

When I do the following I will get my desired result.

DO
BEGIN
    DECLARE identificationnumber_max BIGINT;
    SELECT MAX(identificationnumber) INTO identificationnumber_max FROM newsletter_status;
    EXEC 'CREATE SEQUENCE id_seq START WITH ' || :identificationnumber_max + 1 || ';';
END;

But this looks very bulky and complex for a thing that should be really obvious and easy to implement. This reduces readability. Is there a simpler way to do what I want?

Upvotes: 0

Views: 1170

Answers (1)

astentx
astentx

Reputation: 6751

If you check the documentation and notation definition, you'll find this:

START WITH <start_value>

<start_value> ::= <signed_integer>

This way there's no possibility to have any expression. I cannot find any reasonable argument for this, because there's a possibility to specify subquery in the reset by part, so the sequence still will depend on the tables involved and the same mechanism may be used for starts with.

Anyway, you may consider to use reset by:

CREATE SEQUENCE id_seq
START WITH 1
INCREMENT BY 1
RESET BY (
  SELECT MAX(identificationnumber)+1
  FROM newsletter_status
)

Upvotes: 2

Related Questions