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

[#1]

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??

[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.