on 08-19-2021 11:53 PM
Hi experts,
I need to write and read to/from a local table in UDF / table function. I learned from this thread: https://answers.sap.com/questions/124436/create-temporary-local-table-in-function-on-hana.html , that this is only possible by calling a procedure from the UDF (the procedure contains the CREATE GLOBAL TEMPORARY TABLE statement). Because this procedure only works if it has not the definition clause
READS SQL DATA
I get in my UDF this error message when calling the procedure:
Could not create catalog object: EXPLAIN PLAN and CALL statement are not allowed;
CALL for non read only procedure/function is not supported in the READ ONLY procedure/function:
line 43 col 17 (at pos 1324)
How can I handle this situation?
Regards, Martin
Not sure why temporary tables should be the "best data structure" here.
The problem described can be solved in several ways without temporary tables.
One way would be to use table variables:
begin
data_sys1 = select ... from system1.tableXYZ;
data_sys2 = select ... from system2.tableXYZ;
...
select ...
from :data_sys1
union all
select ...
from :data_sys1
...
end;
This works completely without temporary tables.
Of course, one can go on and stuff the data into arrays - but what for?
Upgrade the system to a version that is currently in support (HANA 1 is out of maintenance) and you get features like DML on table variables.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That works, thanks a lot. HANA 2.0 is already on our sandbox system and soon on production system too - then I will try it with DML on table variables...
User | Count |
---|---|
70 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.