Skip to Content

How to loop and update the local table in hana?

Apr 03 at 06:57 AM


avatar image
Former Member
Hi expert,

Recently, I encounter the question that in calculation view with SQL script that couldn't call the non-read procedure, the requirement as bellowed:

Source table data like:

Account 63010000 / Amount 200

We needed to alpha convert account 63010000 to 0063010000, I have already done the alpha procedure, I want use cursor to loop every single line, then call procedure and update the local table to do the alpha conversion, but the issue was I couldn't do any insert/update in SQL script, is there a work round that can meet my requirement? Looking forward any help from you.

Thanks very much!

Best regards,

Sean cai

10 |10000 characters needed characters left characters exceeded

Hi Sean,

Can you provide more details and also put it logically so that we can understand better what you are trying to do ?



Former Member

Hi Poonam,

Sorry for misunderstanding, the details logic is :

We want do the alpha conversion in SQL script calculation view:

1.In procedure "ZUSER"."test" contains the alpha conversion logic like Account 63010000->Account 0000000066010000, you can check the attachment.

2.In SQL script calculation view will call this non-read only procedure, the error raised, you can check the attachment.

As my understanding , SQL script calculation view couldn't call non-read only procedure, My question is there a work round can meet my requirement? thanks for your help.

* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Florian Pfeffer
Apr 03 at 09:41 AM

Why the error message is shown should be clear by the error message displayed (you cannot call a non-read only procedure in a read-only procedure/function - and a scripted calc. view is read only).

But the question is why you use that low performance implementation you described? It is not clear why you use a cursor, the temporary table and that logic to identify if the column contains only numbers.

If you would have done a search you would maybe have found great blogs about the topic to identify if a column has only number values. Please check the great blog here.

Having that information you could simply do a select to do the conversion. Following a little dummy example (column = the column which contains the value to which you wanna apply the conversion):

select map(locate_regexpr(START '^[0-9]*$' FLAG 'i' IN column)
                  , 0, column
                  , lpad(column, 60, '0'))
from ...

What does the logic: With the LOCATE_REGEXPR function it is checked if the column value contains only numbers. If not the function returns 0. The MAP functions checks on that. If 0 is returned (= not only numbers), the column value is returned unchanged. If <> 0, the leading zeros are added using the LPAD function.


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks very much Florian Pfeffer! I closed it.


why you want to wrap stored procrdure in scripted calc view, why not use wrapper as procedure.

Can we not go away with loop, this can be easily done with one table function with inside logic to padd with zeros for account no, and calling this table function in the outside calculation view. This way you do not need stored procedure call inside calc view.

Fyi , do not use scripted calc view , use table function as scripted calc views are depricated

10 |10000 characters needed characters left characters exceeded