Skip to Content
-1

Creation of HDB Sequence in HANA

How to create a sequence in the below format in hana

String_Current date_Sequence number?

Add comment
10|10000 characters needed characters exceeded

  • 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)

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 13, 2018 at 07:26 AM

    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
    Add comment
    10|10000 characters needed characters exceeded