on 03-13-2015 8:34 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
<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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.