on 05-06-2015 4:52 PM
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"
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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??
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.
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
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
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.