Table Function: Select statement with tablename in variable - Error: scalar type is not allowed

[Former Member]

I have two SQL statements which are almost identical. The only difference is the table to be queried.

So I thought about using a Table Function (Table User Defined Function), (Table UDF) and passing the table name as a paramter:

FUNCTION "SCHEMA"."package::Function" ( in tbl varchar(200) )
...
Select col1, length(col1), left(col1, 6) ...
from :tbl

Unfortunately I get an error during activation:

Could not create catalog object: scalar type is not allowed; line 18 col 7

The same activation error (scalar type not allowed) occurs in a stored procedure.

So what exactly is the problem - and how to fix it??

[lbreddemann · Accepted solution]

This is a common requirement - you want to write the transformation description of the data once and apply it to different data sets with the same structure.

One way to do this is via dynamic SQL which comes with the well-known disadvantages.

Another is to use table functions and SQLScript to hand over the data you like to process.

E.g.

create column table even_users 
    as (select user_name, user_id from users
        where mod(user_id,2) =0);
        
create column table odd_users 
    as (select user_name, user_id from users
        where mod(user_id,2) =1);        
        
-- now we have two distinct data sets with the same structure

create function getUsersUpperCase (
                in_users table (user_name nvarchar(256)
                              , user_id bigint)) 
returns table (user_name nvarchar(256)
              , user_id bigint)
as 
begin
     return select upper(user_name) as user_name,
                    user_id
             from :in_users;   
end;
        
-- the function performs a transformation (simple for this example) and returns a result of the same structure 
-- to use the function, we need a SQLScript context in order to bind the table type input parameter

do begin
  u =  select * from odd_users;
  select 
	    * 
   from 
         getUSersUpperCase (in_users => :u);        
end;

Here I've separated the transformation declaration completely from the source tables without using any dynamic SQL.

[Former Member · Accepted solution]

Thx for sharing Lars. It very sad that the simpler solution is not possible / allowed

[lbreddemann · Accepted solution]

From my point of view this is just consistent with the general concepts of SQL programming. It’s a declarative, static language. Tables are not just data structures that have certain characteristics, but they identify the value set of tuples stored in them.

So, in my example odd_users and even_users might be seen as “user” tables from the view point of a generalisation modelling, that is not a concept available in SQL. Here, both tables contain different value sets.

If you describe a result set (which is what you do when you write a SELECT) then this is always in the context of a specific table.

Dynamic SQL is the DBMS industries’ response to requirements like yours: to perform a kind of half-meta programming in SQL. With dynamic SQL one doesn’t describe a result set anymore, but how to construct such a description.
While it is a very powerful mechanism it does come with drawback comparable with the lack of static type checking in JavaScript compared to TypeScript.

My personal view here is that the answer could simply be to have the application, that runs those similar but different statements, construct the statements as required instead of trying to force the dynamism into the DB layer.
I like code that is easy to understand - generating SQL in the application layer is not hard to understand and easy to debug as it produces static SQL.
On the other hand, dynamic SQL is not as easy to debug and understand and typically the resulting code obscures what the itention of the program are.
When you look at the example I gave again, it loosely follows the idea of the strategy pattern by isolating the intended computation. It’s very obvious what happens here. That’s why I like it.

I’m not even sure that it requires really that much more code in total, but it definitively requires more artefacts and smaller units of “computation-logic”, which again, is a benefit in my books.

To finish this comment, I have the feeling that frameworks like “Hibernate” are aiming to bridge exactly this conceptual gap, where the procedural/oo-minded developer tries to super-impose his/her models onto a SQL database. Having this “interpreter”-layer (just “translating” is not sufficient here - it’s not a dictionary lookup exercise, but a mapping of concepts) is a necessary component in the application stack and probably too often overlooked.

Having said that, I also think that it doesn’t hurt to have a few modelling/programming pattern in SQL up your sleeve that allow for flexibility and abstraction without resorting to dynamic SQL.

[Former Member]

Hi Thorsten,

Maybe you need dynamic SQL for this scenario.

Here an example:

DROP PROCEDURE "PROCEDURE_TEST";
CREATE PROCEDURE "PROCEDURE_TEST"( IN TABLE_NAME  VARCHAR(100), 
                                      IN SCHEMA_NAME VARCHAR(100) ) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
BEGIN 
  DECLARE T VARCHAR(5000); 
  SELECT   'SELECT * FROM  "'||:SCHEMA_NAME ||'"."'||:TABLE_NAME||'"' 
  INTO     T 
  FROM    DUMMY;
  EXECUTE IMMEDIATE :T; 
END;

call PROCEDURE_TEST('yourTable','yourSchema')

Best regards

Houssem

[Former Member]

Thanks for sharing. I'm aware of dynamic SQL. But I thought this is not recommended:

row 3 in the table states From clause - what do they mean?

https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.02/en-US/e97fa06855b346b2973202df71...

[Former Member]

Also I want to put a graphical Calculation view on top of the Table Function - which is not possible with Stored Proceedures - right?

[lbreddemann]

SELECT ... INTO x FROM dummy

really is bad practice and not required in recent HANA versions anymore.

HANA2 SQLScript actually replaces it during compilation to

x := ... 

Just make it easier for everyone reading the code by directly assigning the variable value.

[former_member750083]

DYANMIC FROM TABLE CAN BE ACHEIVED AS BELOW.

method DYNAMIC_TABLE_GET by database function for hdb language SQLSCRIPT options READ-ONLY USING VBAK t000.

declare lv_tabname string;

declare lv_dsel string;

DECLARE lv_sys string;

*Get client

select LOGSYS into LV_SYS from t000 where mandt = :client;

*Get Dynamic table name

lv_tabname = CASE LV_SYS

WHEN 'S4DCLNT900' THEN 'VBAK'

WHEN 'S4Q' THEN 'KNVV'

ELSE 'KNA1' END;

*write a static select query for definition, field types

lt_data = select CLIENT as CLIENT, KUNNR from VBAK;

*Write dynamic select query as string concatinate

lv_dsel = 'select mandt as client, KUNNR from '|| LV_TABNAME;

*Place dynamic query into table

EXECUTE IMMEDIATE lv_dsel INTO LT_DATA;

*Populate final output of AMDP Table function

return select CLIENT, kunnr from :lt_data where CLIENT = :client ; ENDMETHOD.