Anonymous block in SAP HANA

If I would have received a cent for each time in my career I have seen a select within a select within a select, I would probably be in the same financial situation as I am now. However, if I would receive a cent for every time someone executes a subselect in a subselect in the whole world – then I would definitely be in a different financial position.

But what if there was a way to avoid the draconian query in query? wait… that exists ! allow me to present – the anonymous SQL block.

What is it?

An anonymous block is a stored procedure that it is not persisted in the database. it is a procedure which is created at the start of the runtime and destroyed at the end. Because it does not get stored, it does not require a name – making them “anonymous”

Anyone who has worked with stored procedures knows that they are a very strong database tool, which can simplify SQL queries quite a bit. it would however be overkill to create a “named” stored procedure when you are just hunting for some data – That’s where the anonymous block comes in

What can I do with it?

One strong functions of an anonymous block is that it allows you to store intermediate results in a temporary variable on top of which you can launch another select.

practical example

The finance department has a table with the amount which needs to be payed by a certain date. This table can also contain amounts for future dates.

select
    TO_BE_PAYED_DATE,
    AMOUNT,
    case 
       when TO_BE_PAYED_DATE < ADD_DAYS(current_date,-1)
       when TO_BE_PAYED_DATE = ADD_DAYS(current_date,-1) then AMOUNT
       when TO_BE_PAYED_DATE = current_date() then AMOUNT/ 2
       when TO_BE_PAYED_DATE > current_date() then 0
       else 0 -- always supply a default case
    end case as NEW_AMOUNT
from PAYMENTS_EXPECTED;

DO
BEGIN
--- code goes here ---
END;

now let’s fill it up with our code:

DO
BEGIN
-- declare and fill our date variables
declare today date     = current_date;
declare yesterday date = ADD_DAYS(current_date,-1);

-- selecting our end result
select

    TO_BE_PAYED_DATE,
    AMOUNT,
    case 
       when TO_BE_PAYED_DATE < :yesterday then amount
       when TO_BE_PAYED_DATE = :yesterday then AMOUNT * 0.75
       when TO_BE_PAYED_DATE = :today 	  then AMOUNT * 0.5
       when TO_BE_PAYED_DATE > :today     then 0
       else 0 -- always supply a default case
    end case as NEW_AMOUNT
from PAYMENTS_EXPECTED; 

END;

Notice how the code instantly becomes more readable!

It doesn’t stop with readability, it can also make your life easier. Lets demonstrate that in our next example based of our current result.

The finance department comes back and ask us if we can create “buckets” for the values (after conversion):
– bucket for amount between < 25
– bucket for amount between 25 and 50
– bucket for amounts > 50

In the classic SQL example we would have to start working with subqueries. I’m not going to write the classic example out here, because then I would owe myself a cent :).

Lets see how we can solve this with our Anonymous block!

we can simply store the result of our previous query in a virtual temporary table and do a query on top of that virtual temporary table.

DO
BEGIN
-- declare and fill our date variables
declare today date     = current_date;
declare yesterday date = ADD_DAYS(current_date,-1);

-- store the result in table variable (tv)
tv_temp_table = 
	select

            TO_BE_PAYED_DATE,
            AMOUNT,
	    case 
	       when TO_BE_PAYED_DATE < :yesterday then amount
	       when TO_BE_PAYED_DATE = :yesterday then AMOUNT * 0.75
	       when TO_BE_PAYED_DATE = :today 	  then AMOUNT * 0.5
	       when TO_BE_PAYED_DATE > :today     then 0
	       else 0 -- always supply a default case
	    end case as AMOUNT_NEW
	from PAYMENTS_EXPECTED ;

-- select from tv_temp_table and create buckets	
select 
       TO_BE_PAYED_DATE,
       AMOUNT,
       AMOUNT_NEW, 	
       case 
	 when AMOUNT_NEW < 25 then  '<25'
	 when AMOUNT_NEW <= 50 then '25-50'
	 else                       '>50'
       end as BUCKET
from :tv_temp_table;

END;

So what are we doing here?

1. we calculate todays date and we store it in the variable called “today”
2. we calculate yesterdays date and we store it in the variable called “yesterday
3. we apply the logic initially provide by our finance departments based on the date
4. we store the result of the query in a temporary virtual table called “tv_temp_table”
5. because the result is stored in a variable, it will not be outputted to the screen
6. we do a new select on the data in the “tv_temp_table” and put the amounts from our initial query in buckets
7. as we do not store this result in a variable, it will be outputted on the screen

The final result will look like this if today is 2021-01-03:

closing word

Hopefully this simple example demonstrates the power of using Anonymous blocks. It can be used not only to make code more readable, but is also a powerful way of wrangling data and avoiding nested selects in a query. It can also be used to test BW AMDP transformations, but more on that another time.

Further reads

Official HANA documentation on anonymous blocks:
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.03/en-US/52103853351841f6a1e87f40b2686da9.htm

Tutorial (in HANA Studio):
http://www.bestsaphanatraining.com/how-to-run-sap-hana-sql-script-without-a-function-or-a-procedure-t4.html

Tutorial (WEB IDE)
https://developers.sap.com/tutorials/xsa-sqlscript-anonymous.html