0

Let's assume that we have the following input parameters:

  • date [Date]
  • period [Integer]

The task is the following: build the table which has two columns: date and dayname.

So, if we have date = 2018-07-12 and period = 3 the table should look like this:

date      |dayname 
-------------------
2018-07-12|THURSDAY
2018-07-13|FRIDAY
2018-07-14|SATURDAY

My solution is the following:

 select add_days(date, -1) into previousDay from "DUMMY";
 for i in 1..:period do
     select add_days(previousDay, i) into nextDay from "DUMMY";
     :result.insert((nextDay, dayname(nextDay));
 end for;

but I don't like the loop. I assume that it might be a problem in the performance if there are more complicated values that I want to put to result table.

What would be the better solution to achieve the target?

Rufi
  • 2,529
  • 1
  • 20
  • 41
  • Try Googling two things, how to create a numbers table in hana (e.g. http://www.kodyaz.com/sap-abap/create-numbers-table-function-on-sap-hana-database.aspx) and how to use a numbers table to create a list of dates (e.g. https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates). Using a numbers table is the usual method for best efficiency/ flexibility. – Richard Hansell Jul 12 '18 at 14:39
  • Thanks, will try it out. – Rufi Jul 13 '18 at 05:48

2 Answers2

1

Running through a loop and inserting values one by one is most certainly the slowest possible option to accomplish the task.

Instead, you could use SAP HANA's time series feature. With a statement like

SELECT to_date(GENERATED_PERIOD_START)
    FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', '01.01.0001', '31.12.9999')

you could generate a bounded range of valid dates with a given interval length.

In my tests using this approach brought the time to insert a set of dates from ca. 9 minutes down to 7 seconds...

I've written about that some time ago here and here if you want some more examples for that. In those examples, I even included the use of series tables that allow for efficient compression of timestamp column values.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Yes, I can say that this looks very good, the only problem is that I need to use it in the procedure. In this case I use synonym and then I get the database error: feature not supported: field or table alias is not allowed as an input of table functions. Any ideas? – Rufi Jul 16 '18 at 11:44
  • If you use XSA then it’s probably just a missing synonym to the table function. Create a new question for that problem and include your code and HANA version, I’d say. – Lars Br. Jul 16 '18 at 13:34
  • Thanks, Lars, I will. – Rufi Jul 17 '18 at 05:48
0

Series Data functions include SERIES_GENERATE_DATE which returns a set of values in date data format. So you don't have to bother to convert returned data into desired date format.

Here is a sample code

declare d int := 5;
declare dstart date := '01.01.2018';

SELECT generated_period_start FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :dstart, add_days(:dstart, :d));
Eralper
  • 6,461
  • 2
  • 21
  • 27