Record by record loop in HANA

[Former Member]

Hi,

We are writing an SQL in HANA based on an ECC replicated table.  What command is used to loop record by record of the table?

[former_member184768]

Hi Haresh,

What is your exact requirement. There are multiple ways to loop through the records like WHILE / FOR Loops or using Cursors. But it depends on what is the exact requirement.

Please note that record by record processing will have negative impact on the performance and parallel execution.

The loops can be avoided by processing in the select / update / insert statements.

Hence it is important to know the requirement to suggest the better approach.

Regards,

Ravi

[former_member193862]

Hi Ravi,

     I am new to SAP HANA. I understand in SAP HANA there is no ISNUMERIC(). Could you please assit me in writting a function equivalent to ISNUMERIC().

Thanks!

Mirthu

[former_member201560]

Hi Mirthu,

Since we dont have HANA function to check for isnumeric, with reference from old SCN post :

I have created one function to check if the passed value is numeric or not. Just have a look and try yourself in your HANA environment.

create function func_isnumeric(IN A NVARCHAR(100))

RETURNS TABLE(RET_VAL INTEGER)

LANGUAGE SQLSCRIPT AS

temp_ret_value integer;

BEGIN

    select length(ltrim(:A,' +-.0123456789')) into temp_ret_value from dummy;

    RETURN SELECT temp_ret_value as RET_VAL from dummy;

    -- IF RET_VAL is 0, then it is numeric

    -- IF RET_VAL > 0 , it is not numeric

END;

select RET_VAL from func_isnumeric('2322324')

Regards,

Yogesh

[former_member193862]

Thanks a lot Yogesh!!! Will check in hana and let you know... thanks again:-)

[former_member193862]

Hi Yogesh,

              Thanks a lot!!  When I pass a Constant value the function Works fine.. Is it possible to pass a table field.

Regards,

Mirthu

[Former Member]

Hi Haresh

You can refer the same document as mentioned in your previous post, page number 37.Inside procedure you can use while loop or for loop .

  http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Thanks

Santosh varada