The importance of SERIES GENERATE Functions in SAP HANA

[former_member181964]

In this blog, I'm going to explain few SERIES GENERATE functions with simple examples. It looks like simple but we can use this logic/functions instead of using LOOP's in code to process the data.

In general LOOP's will take time to process data, and we all will try to avoid LOOP's in our code. Below example explains how to avoid FOR LOOP and alternatively use SERIES_GENERATE functions.

We are going to see below functions with different scenarios.

1. RAND () : This function is used to generate Random Numbers for a given range i.e. From and To numbers.


2. SERIES_GENERATE_INTEGER() : This function is used to generate Integer Numbers for a given range with Increment Value.


3. SERIES_GENERATE_DATE() : This function is used to generate Dates for a given range with Increment Value.

Apart from above functions, we also use Data Type Conversion functions like TO_INTEGER(), TO_DECIMAL() and ADD_DAYS() to add number of days to given date.

 

In the below section we are going to see complete examples with three scenarios.

Scenario 1:

Generating 5 Random Numbers using FOR LOOP with in Code Block. The below code block will generate Random Numbers in between 1 to 20, in three different forms like...

1. Random Number by default format


2. Random Number as Integer format


3. Random Number as Decimal format

Though the function is same for three times (above three) but it will generate different numbers or it may even generate same numbers also, we don't know because it is Random. The below code is just to generate a random number in between 1 to 20.

If we execute above code it will generate Random Numbers like below...

Same RAND() function for same range i.e. in between 1 to 20 it generated 3 different numbers.

Keep the above logic in Code Block, to generate Random number for 5 times, we need to use LOOP, in this example I'm using FOR LOOP.

The result of above code is, it will generate Random number for five times.

Scenario 2:

Generating same numbers using "SERIES_GENERATE_INTEGER" function. It is simple, fast and less lines of code.

No Code Block

No Declaration

No Loop

With in simple SELECT statement we can write completed logic.

In above code I passed values 1,1,6 for SERIES_GENERATE_INTEGER. first parameter 1 means Increment by, second parameter 1 means Staring Value i.e. FROM, and third parameter 6 means End Value i.e. TO. If we want to generate numbers from 1 to 5, we have to give END Value/Number + 1 for third parameter in above function.

The result of above code is...

Scenario 3:

In the same way we can also generate Dates between given range by using function SERIES_GENERATE_DATE.

The below code generates dates between '2019-1- 1' and '2019-01-15' increment by 1 day.

The below code generates dates between '2019-01-01' and '2019-01-15' increment by 3 days.

We can also give System Dates instead of hard-code. In below Code, FROM Date is Current Date as per SYSTEM and TO Date is Current Date + 10 days.

We can also allow user INPUT for number days i.e. How many days user want to add for Current Date in TO parameter. This is possible by giving Place Holder instead of hard-code.

In TO we have CURRENT DATE, for that we are adding 10 days, so it will be Current Date + 10

 

We can also allow user INPUT FROM, TO Dates and Number days to add.

By looking at above examples with different scenarios, I would like to conclude that, use SERIES GENERATE functions instead of LOOPs. In some cases we may not avoid LOOPs but before writing LOOP statement, just think about SERIES GENERATE functions.