Skip to Content

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

Nov 22, 2017 at 02:31 PM


avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Lars Breddemann
Nov 23, 2017 at 06:20 AM

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.


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)
     return select upper(user_name) as user_name,
             from :in_users;   
-- 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;
         getUSersUpperCase (in_users => :u);        

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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


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.

Houssem Eddine Hariz Nov 22, 2017 at 03:21 PM

Hi Thorsten,

Maybe you need dynamic SQL for this scenario.

Here an example:

  SELECT   'SELECT * FROM  "'||:SCHEMA_NAME ||'"."'||:TABLE_NAME||'"' 
  INTO     T 
call PROCEDURE_TEST('yourTable','yourSchema')

Best regards


Show 3 Share
10 |10000 characters needed characters left characters exceeded

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?


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

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.