SERIES_GENERATE_INTEGER Not working in SAP Datapshere SQL View

[former_member472726]

I am trying to unpivot some data in SAP Datasphere. SAP SQL won't accept the UNPIVOT function, but native HANA does allow the SERIES_GENERATE_INTEGER. Even when I use a 3rd party DB Manager (DBeaver) and use SQL statement on Datasphere content, it works. However then applying that SQL in a SQL View to generate a new unpivotted view faiils with error

Mismatched ‹Number›, expecting ‹Identifier›

It's unclear if it's a different syntax Datasphere needs or simply SERIES_GENERATE_INTEGER is not in their library.

If the latter, how can I unpivot data in Datapshere without resorting to Python or going under the hood into a stored proceedure?

Many thanks

[Abhishek_Hazra]

Hhave you tried SQL table function? Standard SQL query in Datasphere does not support all kind of functions. Switch to SQL script table function & you will be able to use most of the SQL functions you can use in HANA.

[michael_eaton3 · Accepted solution]

You can use SERIES_GENERATE_INTEGER, you have to configure the SQL View to use SQLScript and define the output columns as shown below.