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