Creating SQLScript Table Function views - the hard way

[#1]

SAP Datasphere consultant has to create a SQL Scripting View (Table Function) with dozens of output fields, you can almost see their expression change. I have yet to meet someone who genuinely enjoys that task.

The reason is simple. SAP Datasphere is generally unable to derive the output structure of the generated function automatically (while this is certainly not a trivial capability to implement, it is also the kind of functionality one would reasonably expect from an enterprise-grade data platform), and as a result, the consultant is often forced to define the output structure manually by creating every field one by one, specifying data type, length, and other attributes individually. Repeat that process dozens of times, and what should be a simple development task quickly becomes a tedious exercise.

But Datasphere consultants are used to finding creative workarounds.

For a long time, there was a small trick that helped avoid this manual effort:

1. Create the object as a standard SQL View.
2. Paste the SQL statement that generates the desired output structure.
3. Validate the view so Datasphere generates the output schema automatically.
4. Change the view type to SQL Scripting (Table Function).
5. Adjust the code by adding the RETURN statement and the final semicolon.

This approach could save several minutes of repetitive work and significantly improve the developer experience.

I say could because, for some time now, this workaround has stopped working reliably. In many cases it no longer works at all.

While reviewing SAP support incidents, I found SAP Note, which appears to be related to this behavior. Unfortunately, the note is quite generic. It mentions that output structure generation may fail for complex queries and even warns about the possibility of view corruption. It literally states:

"Avoid validating the SQL script prematurely during development to minimize the risk of corruption."

The recommendation leaves several open questions. What exactly constitutes validating "prematurely"? Why should pressing the Validate button at the wrong moment risk corrupting the view? And, more importantly, why is deriving the output structure still such a fragile process?

This brings us back to the original situation. Either consultants manually create the output structure field by field and lose valuable development time, or they wait until the new AI-assisted service becomes capable of handling SQL Scripting views reliably. Of course, the ideal scenario would be for the existing workaround to work consistently again, without requiring an additional paid service.

I'm curious to know how others deal with this when creating SQL Scripting (Table Function) Views. Do you define the output structure manually, or have you found a better workaround?