Jul 24, 2020 at 01:24 PM

Unable to reset Sequence after delete the data from the table

1189 Views

Hi All,

I have created a table with an 'ID' column and it automatically creates a sequence name "_SYS_SEQUENCE_4231595_#0_#"'.

I know if you delete and reload the data from the table, the sequence won't reset. I am trying to reset the sequence after data is deleted from the table in the procedure.

1. CREATE COLUMN TABLE "ABC" ( ID BIGINT PRIMARY KEY generated by default as IDENTITY,

Material NVARCHAR(100));

2. That's created a sequence "_SYS_SEQUENCE_4231595_#0_#".

3. Delete data from the Table 'ABC'.

4. RESTART the sequence with the syntax:

ALTER SEQUENCE "SCHEMA"."_SYS_SEQUENCE_4231595_#0_#" RESTART WITH 1; But it's throwing below error.

Could not execute 'ALTER SEQUENCE "_SYS_SEQUENCE_4231595_#0_#" RESTART WITH 1' SAP DBTech JDBC: [383]: invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_4231595_#0_#: line 1 col 16 (at pos 15)

5. I am able to reset the sequence which is created from the flow graph(generate serial key) option.

ALTER SEQUENCE "SCHEMA"."EQUFG" RESTART WITH 1;

Would anyone please tell me what I am missing, also I need to use the Restart statement in the procedure later.

I am using the HANA 1.0 Sp12.

Thank you

Sunil Sharma