Creation of HDB Sequence in HANA

[former_member597564]

How to create a sequence in the below format in hana

String_Current date_Sequence number?

[former_member597564]

i am able to build it successfully

i have used the below sequence code and function code

SEQUENCE:

SEQUENCE "Sequences::surrogate" RESET BY SELECT IFNULL(MAX(SURR_ID), 0) + 1 FROM "tables::employees_history"

Function:

FUNCTION "Sequences::Gen"(iv_string nvarchar(5)) RETURNS ev_result nvarchar(256) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN /***************************** Write your function logic ****************************/ SELECT :iv_string || '_' || TO_NVARCHAR(current_date, 'YYYYMMDD') || '_' ||"Sequences::surrogate".NEXTVAL into ev_result from "tables::employees_history"; END;

i am getting error

Could not execute 'SELECT "SP_SP_CONTAINER_1"."Sequences::Gen"('a'.EV_RESULT AS EV_RESULT FROM DUMMY'
Error: (dberror) [257]: sql syntax error: incorrect syntax near ".": line 1 col 47 (at pos 48)

[pfefferf · Accepted solution]

In case the sequence number part must not be a running number per String/Current Date combination you can simply create a scalar function which creates that format for you.

Example:

FUNCTION "MISC"."test.misc.functions::ownSequence" ( iv_string nvarchar(100) ) 
	RETURNS ev_result nvarchar(256)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
  select :iv_string || '_' || to_nvarchar(current_date, 'YYYYMMDD') || '_' || "test.misc.sequences::test".nextval 
  into ev_result 
  from dummy;
END;

The scalar function uses a simple sequence to get a sequence number and concatenates it with a passed string information and the current date (in this case coverted to format YYYYMMDD).

Executing the scalar function like this ...

select "test.misc.functions::ownSequence"( 'testString') from dummy;

... produces a result like this:

testString_20181213_21

[former_member597564 · Accepted solution]

I am still getting error invalid identifier sequence name is not fetching

[pfefferf · Accepted solution]

innovativenaga: Maybe you can show your coding instead of just saying you get "still" an error.

[former_member597564 · Accepted solution]

SELECT :iv_string || '_' || TO_NVARCHAR(current_date, 'YYYYMMDD') || '_' ||"Sequences::surrogate".NEXTVAL into ev_result from dummy;

error i am getting,

The file requires "db://DUMMY" which is not provided by any file

[pfefferf · Accepted solution]

Are you working with XS Classic or XS Advanced?

[former_member597564 · Accepted solution]

XS Advanced

[pfefferf · Accepted solution]

Ok, then you have to define a synonym for SYS.DUMMY in addition. You can check the section "Simple Example" in blog post https://blogs.sap.com/2017/01/06/synonyms-in-hana-xs-advanced-introduction/ for that.