SAP Datasphere - SQL Views as SQL Script – Automatic generation of columns in the left side pane

[anna_sch-25]

Hi everyone,

I am working with SQL Views using SQLScript (Table Function) in SAP Datasphere and noticed that the output columns must always be created manually in the Model Properties → Columns section before the SQLScript can be validated or deployed.

Even when the SQLScript contains a simple RETURN SELECT ... statement, Datasphere does not automatically generate the column definitions based on the SQL result. As far as I can see, this is different from standard SQL views, where column inference works automatically.

My questions:

1. Is there any way to automatically generate the output columns in the left side panel when using SQLScript (Table Function) in SAP Datasphere?
2. If not:
   • Is this a current technical limitation?
   • Is there any roadmap indicating that automatic column generation will be supported in the future?

Context / What I tried:

• SQLScript with RETURN SELECT ...
• SQLScript with table variables
• Validation to trigger auto‑generation
• Switching between SQL Standard and SQLScript

In all cases, Datasphere requires me to manually maintain the business/technical names and data types in the Model Properties panel before validation succeeds.

Example SQLScript:

return

with "cte_customers" as (
    select
        "id",
        "name"
    from "customers"
)

, "cte_customers_ranked" as (
    select
        "id",
        "name",
        1 as "rank_number"
    from "cte_customers"
)

select
    "id",
    "name",
    "rank_number"
from "cte_customers_ranked";

Expected behavior (wish):

Some way to auto‑populate the column list based on the SQLScript output (similar to SQL Standard views).

Actual behavior:

Columns must be fully defined manually, otherwise validation fails.

If anyone has additional insight, workaround ideas, or official SAP statements regarding this behavior, I would really appreciate it.

Thank you.

Kind regards,
Anna

Clicking Validate for the following statement automatically generates the column ID:

tab =
select
    "id" AS ID
from "customers";

return
select ID from :tab;

Clicking Validate for the following statement does not automatically generate the column ID:

tab = with "cte1" (ID) as (
    select
        "id" AS ID
    from "customers"
)
select ID from "cte1";

return
select ID from :tab;

Is there an issue with the WITH clause?

[CLTGravesen]

Hey Anna,

The short answer is that the SQL editor is very - ehm - simple (but I'm sure it has a great personality).
The Editor will try to derive the columns from whatever it finds immediately after the "Return" statement. 
It will put no effort into it, and any logic between the output and the source will break it. 

An example:

Any kind of obfuscation between the editor and the source tables will result in the editor throwing it's hands in the air. 

Here is a really basic example with a simple temp table between the source and the output:

Even this simple script confuses the poor things. 
It does derive the columns, but everything is cast as a string(100).

Adding a new "Ranking" column, which is clearly an integer, will still result in a string(100).

A workaround (not really).

If you already have an object with the structure you want; one thing that makes it less-awful, it to add a 

return select * from source

And validate. That will generate the structure and derive the schema correctly. 

You can then write your script on top, and replace the "Return" statement later.
This is shaky at best, but it's the least awful way I found. 

[anna_sch-25]

return select * from source is not a workaround for complex sql queries with multiple joins.

[anna_sch-25]

Thank you for your reply. I know the not-really-a-workaround with "return select * from source" for simple queries. It's not working with complex SQL queries with multiple joins and same column names in different tables. Do you know where this feature could be requested?

[CLTGravesen]

I couldn't agree more 🙂