How to loop and update the local table in hana?

[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

[former_member188770]

Hi Sean,

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

Thanks

Poonam

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

[pfefferf · Accepted solution]

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.

Regards,
Florian

[Former Member · Accepted solution]

Thanks very much Florian Pfeffer! I closed it.

[akshinde]

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