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.

[former_member472726]

Thank you for your answers, that has worked!

Interestingly as I was unable to get this working initially, I went about unpivotting an alternative way. I used a UNION of the table multiple times for each individual field I wanted to unpivot.

I have now built same view but using series generate integer.

I have now compared performance of both methods and interestingly the UNION (40 times!) is much more performant than the series generate integer.

I'm just commenting here on my findings in case anyone looks at this question for their own answers to UNPIVOT in SAP Datasphere, in my case, the UNION method is far more efficient, so worth trying out both if anyone has similar use case.