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

[TuncayKaraca]

Hello Samrat,

If I'm not mistaking you are referring to this Informatica Sequence Generator transformation

My first thought while creating Data Flows you would create a calculated columns with Projection operator and using some functions available, check out Function Reference for Data Transformation Language  But unfortunately there is no sequence generator like functions among --seems to be 84 in total-- functions.

There are two options left:

1. You will use Script operator and generate number with Python script in still Data Flow > Projection > Calculated Column. Check out Script Operator Python Reference
2. Create SQL View and you would use ROW_NUMBER Window function.

@XaviPolo answered one similar question here Add a Column with progressive number in Dataflow (Datasphere)

Regards,
Tuncay

[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