0

I have been banging my head once again on HANA syntax. I have been looking for a method of writing either a function or procedure and calling the function or procedure in a select statement to evaluate a column in a table and alter the column based on an if function.

I have created most of the script but the replace function is not working as expected. I am not too familiar with HANA, so any help would be appreciated. Thanks.

Can someone also let me know how can I call the procedure as this seems a bit more complicated in HANA? I am using HANA sp 10.

I am essentially wanting to run the function or procedure using a select statement and output the result as follows

example of what I am trying to achieve

id string updated temp_str from function or procedure
1 12212 12,2,12
2 21221 2,12,2,1
3 12212 12,2,12 
create procedure update_str   
language sqlscript   
as   

ip_str varchar:= '21222212';    

temp_str varchar(100) := ip_str || ',';   
pos integer :=1;   

begin   

while(length(:temp_str) > 0 ) do   

if substr(temp_str,1,1) = '1' and substr (temp_str,2,1) = '2' then   
update temp_str := replace(temp_str,'12','12,');   
pos := :pos + 1;   

elseif substr(temp_str,1,1) = '2' and substr (temp_str,2,1) = '1' then   
update temp_str := replace(temp_str,'21','2,1');   
pos := :pos + 1;   

elseif substr(temp_str,1,1) = '2' and substr (temp_str,2,1) = '2' then   
update temp_str := replace(temp_str,'22','2,2');   
pos := :pos + 1;   

else;   

end if;   
end if;   
end if;   

end while;     

end;
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
MRUNIVERSE
  • 31
  • 1
  • 6

1 Answers1

1

For what you described it is best to use a scalar user-defined function (SUDF).
How to create and use those is explained extensively in the SAP HANA Developer Guide, so I won't go into details here.

I also won't discuss the logic errors in the provided code, instead here is a version that generates the output for your test data:

drop function update_str;
create function update_str (IN IP_STR varchar(100) ) 
        returns res_str varchar(200)
language sqlscript   
as   
begin   
declare temp_str varchar(100) := ip_str ;   

    -- add a comma behind twelves
    temp_str := replace (:temp_str, '12', '12,');

    -- add a comma between twenty-ones
    temp_str := replace (:temp_str, '21', '2,1');

    -- add a comma between twenty-twos
    temp_str := replace (:temp_str, '21', '2,1');

    -- remove last comma if there is any
    if (right (:temp_str, 1) = ',' ) then
        temp_str =  left (:temp_str, length(:temp_str) -1 );
    end if;

    res_str :=  :temp_str;
end;

Check the code:

with test_data as 

          ( select 1 as id, '12212' as str from dummy
union all select 2 as id, '21221' as str from dummy
union all select 3 as id, '12212' as str from dummy)
select id, str, update_str(str)
from test_data;

ID  STR     UPDATE_STR(STR)  
1   12212   12,2,12        
2   21221   2,12,2,1       
3   12212   12,2,12

Depending on your actual requirement you might be able to form a regular expression that performs the same transformation. If so, you could also use the REPLACE_REGEXPR function in SAP HANA.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29