Looping in SQLScript Like ABAP

[william_andradebatista]

Hi All,

Today I am here to quickly show you a useful technique for making loops (index-based cell access) in SQLScript the same way you can in ABAP. For those of you who are “HANA Beginners” and have a hard time with from/to, I would like to say that in most scenarios with small amount of data it will be fine and mainly for those you need to create registers amongst and/or other few scenarios…

Bear in mind that this technique has bad impact on performance for large amount of data and should be used as an exception and in specific cases where a DML/DQL is not enough...

Whenever possible try to put your logic on DQL/DML statements, cause HANA has engines which processes data in optimal way and considerably faster...

Initially, it was hard for me to find an alternative in a straightforward text for the most common ways to do loops: cursors and select with offset.

Let´s get to it:

Given a scenario where we have a payroll table in which we want to give a salary raise of 20% to employees which were hired 10 or more years ago, and 10% to employees hired less than 10 years ago … the table is shown below:



If we were developing an ABAP, I would do this (I am assuming that the table is already created and populated):



How we would translate the above code to a HANA SQLScript procedure?

Using anonymous block we can code an SQLSCript into HANA’s SQL Console directly…

IMPORTANT REMARK: All code below was written in version 1.0 SPS12(1.00.122.15.1516128271)



From HANA 2.0 SPS00 on, instead of this:



you can use operator RECORD_COUNT, to accomplish the same “old school” task in an updated and modern style:



Another important remark is that there are some places in the code where you will be unable to use index-based cell access directly, such as WHERE clauses etc. For these scenarios you can assign the table cell to a variable and then use this variable wherever it is needed.

After executing the above code, here are the results:



Thanks and see you in next post!

P.S. If you wish to reproduce the above test in SAP HANA, here are the codes to make things easier, just in case ?

***************************** SQL DDL/DML ******************************

CREATE TABLE “S0003358391″.”PAYROLL” (
“ID” INTEGER CS_INT,
“NAME” NVARCHAR(30),
“DEPARTMENT” NVARCHAR(30),
“HIRE_DATE” date,
“SALARY” decimal(15,2),
PRIMARY KEY (“ID”)) UNLOAD PRIORITY 5 AUTO MERGE

insert into “S0003358391.”PAYROLL” values(1,’JOHN’,’SALES’,’20170101′,10000);
insert into “S0003358391″.”PAYROLL” values(2,’LEONARDO’,’SALES’,’19700101′,50000);
insert into “S0003358391″.”PAYROLL” values(3,’WILLIAM’,’IT’,’19800101′,5000);
insert into “S0003358391″.”PAYROLL” values(4,’ALLAN’,’IT’,’19900101′,45000);
insert into “S0003358391″.”PAYROLL” values(5,’FABIO’,’IT’,’20000101′,30000);
insert into “S0003358391″.”PAYROLL” values(6,’MARCELO’,’MARKETING’,’20150101′,45000);
insert into “S0003358391″.”PAYROLL” values(7,’ANDRE’,’MARKETING’,’20150101′,35000);
insert into “S0003358391″.”PAYROLL” values(8,’VIVIAN’,’RH’,’20140101′,30000);
insert into “S0003358391″.”PAYROLL” values(9,’PAULA’,’RH’,’20140101′,30000);
insert into “S0003358391″.”PAYROLL” values(10,’MACIEL’,’IT’,’20180101′,30000);

You can replace schema “S0003358391” with the one corresponding to your user or any of your preference

***************************** SQLSCRIPT CODE ******************************

DO

BEGIN

declare v_years integer;

declare v_count integer;

declare v_i integer;

sel1 = select * from “S0003358391″.”PAYROLL” order by hire_date;

select count(*) into v_count from :sel1;

for v_i in 1..v_count do

if ( months_between(:sel1.hire_date[v_i], to_date(now()))/12 ) >= 10 then

sel1.salary[v_i] = :sel1.salary[v_i] + ( :sel1.salary[v_i] * 0.2 );

else

sel1.salary[v_i] = :sel1.salary[v_i] + ( :sel1.salary[v_i] * 0.1 );

end if;

end for;

upsert “S0003358391″.”PAYROLL” select * from :sel1;

END;