on 10-31-2014 7:40 PM
Can initialize the value for the sequence (_SYS_SEQUENCE_) used for an identity column?
Thank you
You can use the following sql statement:
CREATE SEQUENCE SCI."SEQUENCE1" START WITH 1;
SELECT SCI."SEQUENCE1".NEXTVAL FROM DUMMY
Reference: CREATE SEQUENCE - SAP HANA SQL and System Views Reference - SAP Library
Regards,
Pablo Silva
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Pablo
The indicated option does not takes advantage of using identity columns in
HANA SPS8. To implement identity columns in HANA SPS8, system sequences are
created (_SYS_SEQUENCE_). For these sequences I can indicate an initial
value using the clause: "generated by default as identity (start with xx)". I
need to know, how to change the "NEXTVAL" value, after the table (using
identity column) was created.
Thanks in advance
2014-11-06 3:01 GMT-06:00 Pablo Silva <experiencesaphana@sap.com>:
SAP HANA <http://www.saphana.com/index.jspa> Initialize the value
for an identity column - HANA SPS8? created by Pablo Silva
<http://www.saphana.com/people/C5185151> in SAP HANA One Support - View
the full discussion <http://www.saphana.com/message/13604#13604>
Got it.
There are two ways a sequence can be reset:
This sequence name I got from
But I also got the following error message when trying the first approach:
CREATE COLUMN TABLE TEST_TABLE (ID integer GENERATED BY DEFAULT AS IDENTITY(start with 2),COL2 VARCHAR(2));
select '"' || SCHEMA_NAME || '"."' || SEQUENCE_NAME || '"' AS SEQUENCE_NAME from SEQUENCES WHERE RESET_BY_QUERY LIKE '%TEST_TABLE%';
INSERT INTO TEST_TABLE(COL2) VALUES('r1');
INSERT INTO TEST_TABLE(COL2) VALUES('r2');
alter sequence "UADMIN_USER"."_SYS_SEQUENCE_210253_#0_#" restart with 1
Could not execute 'alter sequence "UADMIN_USER"."_SYS_SEQUENCE_210253_#0_#" restart with 1' in 259 ms 481 µs .
SAP DBTech JDBC: [383] (at 29): invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_210253_#0_#: line 1 col 30 (at pos 29)
Let me know if you find something better.
Regards,
Pablo Silva
There is a much easier way to reset/set the sequence:
A: determine the current value: select schema.sequencename.CURRVAL from dummy; 5
B: determine the value you want to start with (mostly a SELECT MAX(column) from table) for example 10
set the next value:
select schema.sequencename.NEXTVAL+6 from dummy
this will set the currval to 11 (10-5 +1)
NEXTVAL is (as CURRVAL) a function for the sequence. NEXTVAL sets the value. where CURRVAL gives you the value of the sequence.
Right but the whole point is to reset the auto generated sequence by the ID column set as identity to auto increment. Those auto generated sequences are not allowing you to update them to reset. If you can reset it, then you can carry on with any insertions without providing any IDs as usual. I think he is trying to cover the case where you need to sometimes insert with some fixed IDs rather than using the next value.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.