How to create a sequence number in SAP Datasphere

[samratmuddasani]

Hi Everyone,

How to create a sequence number in SAP Datasphere transformation flow just like how we create in Informatica?

Thanks,
Samrat Muddasani

[SiarheiShydlou]

     Hello Samrat,

There is one more workaround which will not work in Transformation Flow but can be used in Data Flow. There is a way to use HANA database sequence:

1) You have to create Open SQL schema and there create a new sequence, e.g.
CREATE SEQUENCE mySeq2 START WITH 1;

2) Then create a table function wrapper for this sequence (since sequence can not be directly included into a view), e.g.

CREATE FUNCTION myFunc()
RETURNS seqID decimal
LANGUAGE SQLSCRIPT AS
BEGIN
SELECT mySeq.NEXTVAL INTO seqId FROM DUMMY;
END;

3) Create a view based on the function, e.g.
CREATE VIEW myView AS (SELECT myFunc() AS SEQID FROM DUMMY);

4) Next step is in Datasphere Data Builder. Start creating a graphical view, drag the view from the Open SQL schema - Datasphere will ask to create a local table pointing to the Open SQL Schema view.
After that you can create a SQL view where you combine source table with the view table in a cross join:
SELECT A.*, B.SEQID FROM <based table> AS A, "MYVIEW" AS B

And this final view can be used in Data Flow, column SEQID will give you a unique sequence number