One sequence for several tables

[Wineman]

Hi,

I need to use ONE sequence in the create statement for several tables (it's a partner solution, where we just want to replace the exising DB in the first step).

I created a sequence:

CREATE SEQUENCE ww_seq

    START WITH 1

    INCREMENT BY 1;

and then I would like to use this sequence in that way as described in the docu

create column table "bla1" (no1 integer generated by default as ww_seq.nextval, text1 varchar(10));

create column table "bla2" (no2 integer generated by default as ww_seq.nextval, text2 varchar(10));

Is this possible? If yes, what is the syntax?

thanks a lot

Wolfgang

[Former Member]

Hi Wolfgang,

You can have a look at CREATE TABLE - SAP HANA SQL and System Views Reference - SAP Library, please search col_gen_as_ident

Best regards,

Wenjun

[Wineman]

Hi Wenjun,

thanks a lot, but I checked this already 🙂

create column table "bla1" (no1 integer generated by default as ww_seq.nextval, text1 varchar(10));

gives the error message 

sql syntax error: GENERATED BY DEFAULT is allowed for identity column

create column table "bla1" (nr1 integer default ww_seq.nextval, text1 varchar(10));

gives the error

sql syntax error: incorrect syntax near "ww_seq"

create column table "bla1" (no1 integer generated by default as identity ww_seq.nextval, text1 varchar(10));

gives the error

sql syntax error: incorrect syntax near "ww_seq"

I also tried some other variants, nothing works.....

best regards

Wolfgang

[Former Member]

Hi Wolfgang,

As you can see the syntax, AS IDENTITY is a must, not optional. But (<sequence_option>) is optional. So, you need to follow the syntax.

 <col_gen_as_ident>    ::= GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(<sequence_option>)]

Best regards,

Wenjun

[Former Member]

 <col_gen_as_ident>    ::= GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(<sequence_option>)]
 <sequence_option>     ::= {<sequence_param_list> | RESET BY <subquery> | <sequence_param_list> RESET BY <subquery>}
 <sequence_param_list> ::= <sequence_parameter>[{,<sequence_parameter>}...]
 <sequence_parameter>  ::= START WITH <start_value>
                         | INCREMENT BY <increment_value>
                         | MAXVALUE <max_value>
                         | NO MAXVALUE
                         | MINVALUE <min_value>
                         | NO MINVALUE
                         | CYCLE
                         | NO CYCLE
                         | CACHE <cache_size>
                         | NO CACHE
 RESET BY <subquery>

Here I pasted the complete syntax, as you can see, you don't need to first create a sequence manually. You can just input some sequence parameters, since using "AS IDENTITY" will create an implicit sequence.

You can also find an example at the bottom.

 CREATE COLUMN TABLE T (A INT, B INT GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10));

Best regards,

Wenjun

[Wineman]

Hi Wenjun,

thanks very very much for your help, but unfortunately it's not that what I am looking for.

I would like to create a sequence and use this sequence in the CREATE TABLE command of different tables.

I managed to create a table to use the sequence in the insert command, this works, but I don't get in run with the create table statement, although it's described.

this works here:

create column table "bla1" (no1 integer, text1 varchar(10));

insert into "bla1" values (ww_seq.nextval, 'hugo');

best regards

Wolfgang

[lbreddemann]

Hi Wolfgang,

would've saved some back and forth if you had mentioned what you had tried already...

Anyhow, specifying a predefined and shared sequence is not possible for the IDENTITY column.

Besides the additional dependency between two tables introduced by this (which might be cumbersome for maintenance ... e.g. think of the a possible RESET BY clause that now needs to span multiple tables to find the next possible value), it simply doesn't work with the way the IDENTITY column feature is implemented in SAP HANA.

The partner solution will likely need to be adapted as they are using a DBMS specific feature (also see Comparison of different SQL implementations on that).

- Lars

[ruediger_karl]

Hi,

sequences are not allowed as default values for columns in a create/alter table statement. It's documented in the SQL ref guide.

Regards, Ruediger

[Former Member]

Hi Wolfgang,

This is what I thought!

create column table "bla1" (no1 integer generated by default as ww_seq.nextval, text1 varchar(10));

create column table "bla2" (no2 integer generated by default as ww_seq.currentval, text2 varchar(10));

So during next insert in "bla1", you can again call ww_seq.nextval.

Thanks,

Monissha