cancel
Showing results for 
Search instead for 
Did you mean: 

Usage of Stored procedure in Calculation View

Former Member
0 Kudos

Hi Experts,


I have created a non read only procedure in HANA using SQL Console, But when i try to use that in my calculation view, I still get an error that it is read only procedure. I havent written "READS SQL DATA As" in my procedure, So it should be a read/write procedure. Could you please help me with any alternative?



Code :

create procedure "MYSCHEMA".PROCEDURE_TEST()

language sqlscript as

begin

DECLARE tname varchar(50);

     SELECT TABLE_NAME into tname FROM "MYSCHEMA"."TEST_TABLE" WHERE ID ='5';

    

     execute immediate ('select'||'*'||'from'||tname) ;

END;

In calculation View : 

EXEC 'call "MYSCHEMA".PROCEDURE_TEST();

call "MYSCHEMA"."PROCEDURE_TEST"(varout);

Tried Both, But getting the error  for both syntax as below :

"CALL for non read only procedure/function is not supported in the READ ONLY procedure/function"

0 Kudos

If we can't use stored procedure under calculation view ,then what is the use of stored procedures in SAP HANA.

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos
Hi Jyotsna,
You can achieve your requirement by creating HANA Table Function. Table function can be called from Calculation view by adding projection or aggregation nodes. You can 
write SQL statements in the function. 
Mainly, HANA stored procedures are used for DML (insert/ update/ delete)  
operations.

Regards
Kedar
Former Member
0 Kudos

This message was moderated.

former_member185132
Active Contributor
0 Kudos

The error is saying that you cannot call a non read-only procedure (PROCEDURE_TEST) from a read-only procedure (Calc Views are by default read-only).

This makes perfect logical sense - a read only procedure is obviously supposed to only read data, that's the basic definition. The idea is that the execution of a read-only procedure must not change any data in the system. Now if it had the capability to call a non read-only procedure, that makes the above definition entirely invalid. The very term "read-only" becomes meaningless.

So basically, the system will only allow you to call a read-only procedure from another read-only procedure. Instead, please rewrite PROCEDURE_TEST to be a read-only procedure.

Former Member
0 Kudos

Hi Thanks for the reply.

But for my logic I should definitely write execute immediate statement which is a dynamic statement and is not allowed in Read only procedure.

When i try to include the syntax : reads sql data in my syntax I get an error while activating the procedure stating that execute immediate is not allowed.

Any more thoughts??

former_member185132
Active Contributor
0 Kudos

Looking at your code, there's no need for exec_immediate.

Current code:


     SELECT TABLE_NAME into tname FROM "MYSCHEMA"."TEST_TABLE" WHERE ID ='5';  

     execute immediate ('select'||'*'||'from'||tname) ;

This cab be replaced by:


    tname = SELECT TABLE_NAME FROM "MYSCHEMA"."TEST_TABLE" WHERE ID ='5';  

In fact if this is all you have in the stored proc, you can discard the stored proc itself and put the select statement directly in the CV.

Former Member
0 Kudos

Hi,

Thanks for the reply.

We have a peculiar requirement and hence we have to use a different code.

TEST_TABLE would contain a single row which would be updated every day. This another table name in "TABLE_NAME" field. Finally we want to get the result out of the table that is stored in TABLE_NAME field.

To keep it short, TABLE_NAME can contain TABLE1 or TABLE2 or TABLE3 ..., So on the fly calculation view should hit different table every day based on the update we do on TEST_TABLE.

I am also not able to  get result with inner query :

SELECT * FROM (SELECT TABLE_NAME FROM "MYSCHEMA"."TEST_TABLE" WHERE ID ='5');

Any thoughts?

-vishwa

former_member185132
Active Contributor
0 Kudos

I would suggest to modify the design.

The TABLE1, TABLE2, TABLE3 etc in all probability all have identical (or almost identical) structures. So identify this list of tables. Now create a Calc View (call it CV1) as a union of all these tables with constant values. Also include one constant column, refer this doc for details

In this case, the constant value for each table would be the table name itself. So in the constant column, create the mappings and define the constant value for TABLE1 to be "TABLE1", for TABLE2 to be "TABLE2" etc.

Now, modify the stored procedure as follows:


SELECT TABLE_NAME into tname FROM "MYSCHEMA"."TEST_TABLE" WHERE ID ='5';

SELECT * FROM _SYS_BIC.<<CV1>>

     WHERE <<constantField>> = tname;

Now there is no need for any non-read-only code.

Regards,

Suhas

Former Member
0 Kudos

Hi

But we have a problem here. TABLE1, TABLE2 and TABLE3 are system tables that would be generated and wouldnt know tha table names in advance

former_member185132
Active Contributor
0 Kudos

Can you explain your requirement in more detail? There might be a different way to achieve it.