Skip to Content

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;

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Walter Müllner

    Why would you like to do a “select * from <table>” in an sql-script procedure? This is what exactly should be avoided not to kill HANA performance.

     

    Why not just use two update-statements: (in the correct SQL terminology, of course):

         “update payroll set salary=salary*1.02 where months_between(hiredat, now) >=12”

    and another one with

         “update payroll set salary=salary*1.01 where months_between(hiredat, now) < 12”

    ??

     

    br, WM

     

    (1) 
    1. William Andrade Batista Post author

      Hi Walter,

      Thanks for your comments!

      Yeah, I agree with you about performance issues using loops, that’s why I put a proper remark in the beginning 😉 95% of cases this is not recommended, but in 5% there are scenarios that only using loops you can solve some problems for example: In my first experience, I had to convert a interval based table to a CALMONTH(YYYYMM) table and scenarios like this I don’t know other way without use of loops. At my start, was hard to find a straightforward information as I set out to do above…..

      Regarding “update”, apparently, you have not understood this post objective: For abapers that do not know anything about SQLScript(my case one year ago) and any other beginner used to traditional databases. For those people is usual this approach of loops since they do not have neither tables in-memory nor in most cases SQL/Join engines… they need to pull data to memory and then process it through loops 🙂 Second, in the way I put I am sure it will help them to get familiarized faster than try change the mindset to a DQL/DML approach and last but not least I wanted to introduce 3 concepts: Anonymous blocks, Index-based cell access and loops using for..endfor and I choose to be as simple as possible with salary raise scenario… If I followed your suggestion, my post would be pointless…

      So this post is not intended to teach Best Practices on use of DML/DQL for advanced users in SQLScript… sorry if I disappointed you…

      All the best,

      William

       

      (0) 

Leave a Reply