How to use temporary table for table function?

[martin_lehmann4]

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

[lbreddemann]

Can you explain why you need to write to al local (temporary?) table in a function?

If the purpose is to change and persist data permanently then using a procedure would likely be the better option.

If three purpose is to transfer intermediate results between different functions and queries, using table typed parameters and table variables could be what you’re looking for.

To make that decision the use case needs to be described in more detail, though.

[martin_lehmann4]

The use case is as follows:

- A BEx query on Composite Provider reads from Calculation View with Table function (BWoH hdb release 1.00.122.27.1568902538) the number of sentences of the same table in five different ERP systems - these systems are connected to hdb via SDA views. Parameters (IP parameter) are company code, fiscal period and system.

- Not all company codes are in more than one system, so the best data structure to collect the data from the different system would be a temporary table (according to internal table in ABAP) to collect - if necessary - the following information:

BUKRS VARCHAR(4), 
CLNT NVARCHAR(3), 
FISCYEAR NVARCHAR(4), 
FISCPER3 NVARCHAR(3), 
NO_SENT_SYSTEM1 INTEGER, 
NO_SENT_SYSTEM2 INTEGER, 
NO_SENT_SYSTEM3 INTEGER, 
NO_SENT_SYSTEM4 INTEGER, 
NO_SENT_SYSTEM5 INTEGER 

Reading in SAP help I just wonder whether I could manage this task with the help of ARRAY variables and a table creation by an UNNEST function? ( -> https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/1.0.12/en-US/8d4be75f708c4492ab22b6b647... )

[lbreddemann · Accepted solution]

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.

[martin_lehmann4 · Accepted solution]

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