SAP Datasphere Data Modelling Tips: Using SQL Script Table Function

In SAP Datasphere , you can write SQL Script Table Function which is the equivalent of Common Table Expression (CTE) in SQL.

Screenshot, GUI and information as of Sep 2025.

Where To Find?

Date Builder --> New SQL View

Once you click on the "New SQL View" option, in the model properties you can select "SQLscript (Table Functions)" from the drop down.

Writing a SQLScript Table Function

1] Simple example

RETURN
    SELECT "ebeln" AS "ebeln1" 
    FROM "EKKO" 
    WHERE "ebeln" = '123456'
;        

Note the semi-colon at the end.

For Datasphere to display the results:

A] You need to specify the OUTPUT columns.

Click the pencil icon in "Columns" section and enter the Business and Technical Names.

Note that I have the technical name same as in the SELECT statement column alias "ebeln1".

If you don't setup the columns in Column Properties and click on the "Validate SQL" icon, then a default column will be created. The Business Name will be based on the table used in the query.

b] Deploy the code.

You can now see the results.

Note the column name of the result is same the Business Name you provided in Properties column definition.

If you change the Business Name in the Properties and keep the same technical name of the column alias, then the results set will reflect the new name.

If the Technical Name in the Column alias is different in the Column properties, then Datasphere will throw an error.

2] Advance Example

A more advance example is where the SQL CTE type code is needed.

RETURN
WITH "Header1" AS (
    SELECT "ebeln" AS "ebeln1" 
    FROM "EKKO" 
    WHERE "ebeln" = '123345'
),
"Header2" AS (
    SELECT "ebeln" AS "ebeln2" 
    FROM "EKKO" 
    WHERE "ebeln" = '123345'
)
SELECT 
    H2."ebeln2" AS "ebeln1" 
FROM "Header2" H2
JOIN "Header1" H1 ON H1."ebeln1" = H2."ebeln2";        

As you can see I have kept the same column alias as the Technical Name originally specified in the Columns properties.

Note the semi-colon at the end of the final SELECT statement.

Space In Column Technical Name

Something interesting to note is when you have a space in the Technical Name when you enter the SQL query and deploy.

RETURN
    SELECT "ebeln" AS "PO Number" 
    FROM "EKKO" 
    WHERE "ebeln" = '12345'
;        

The column technical name in the Column Properties will have the space.

Generally space is not allowed in Technical Names when you create using Column Properties option. But in this particular scenario Datasphere creates the column with space in Technical Name.

Sometimes there are issues when using the query with space in the technical names in other Views or Transformation Flows, but I could not recreate the issue.

Data Access Control

There are other useful features such Data Access Control can be applied to the SQLScript Table Function view. I have not explored applying DAC to SQLScript Table Function yet.