SQL script append/insert to temporary table in scripted Calc. view

[Former Member]

Hi,

We have requirement to show Number of active customers by Calendar date in reporting. So using Valid From and Valid to we are determining number of Active Customers for the particular calendar date for last two months ( for every date we need to find respective values in view).

For this purpose we are creating Scripted calculation view. But we cannot get full result set  we are getting only last loop results. My assumption is this is happening because :table variable is reference and it contains only last result set.

Is it possible to append/insert result set to some temporary table and only then assign to var_out?

Is there other way of achiving this requirement?

Following code is added:

DECLARE lv_analisys_start date;

DECLARE lv_today date;

DECLARE lv_date date;

DECLARE lv_int integer := 0;

DECLARE rowcount integer; 

lv_analisys_start := to_date(left(to_dats(now()), 6)-1);

lv_today := current_date;

dates_table = select "DATE_SQL" from "_SYS_BI"."M_TIME_DIMENSION"

where date_sql >= :lv_analisys_start AND date_sql <= :lv_today;

SELECT count(*) into rowcount from :dates_table;

WHILE :lv_int < rowcount DO

select "DATE_SQL" into lv_date from :dates_table LIMIT 1 OFFSET :lv_int;

table =  select "PRDNR", "CITY",

sum(case when to_date(left("VALFR",8)) <= lv_date

AND to_date(left("VALTO",8)) >= lv_date then 1

else 0

end) AS "CM_ACTIVE_CUSTOMERS", lv_date as DATE

FROM "_SYS_BIC"."sap.development/CA_CUSTOMER_DATA"

group by "PRDNR", "CITY";

lv_int := :lv_int + 1;

var_out = select * from :table;

END WHILE;

Thanks,

Markus

[lbreddemann · Accepted solution]

You're mistaken Markus.

Your requirement is not to append/insert to a temporary table, but to search before you post a new question. This specific question had been asked, discussed and answered quite often so far.

I decided not to moderate it "away" because I think the use case you present could be interesting for some discussion:

What do is your expected output?

A list containing every date and the number of inactive and active customers per city per product, right?

Do you think looping over all dates in the range is the best approach here?

Why the double/triple data type conversion of now()?

Why the WHILE loop? What's wrong with the FOR CURSOR LOOP?

Why not be explicit about the expected output structure and avoid SELECT * FROM? It's only three columns anyway...

- Lars

[Former Member · Accepted solution]

Hi Lars,

Answering your questions:

 What do is your expected output? 
A list containing every date and the number of inactive and active customers per city per product, right?

Expected output: last two months and the number of active customers per city per product for each date.

Do you think looping over all dates in the range is the best approach here?

I will be glad to hear another approach, but as i need to do calculation for each date separately, I do not see another one. Please kindly share if you have one.

Why the double/triple data type conversion of now()?

actualy should be like that: add_months(left(to_dats(now()), 6)-1);

any concerns? Please advice.

Why the WHILE loop? What's wrong with the FOR CURSOR LOOP?

After reading this article i decided to use WHILE ( WHILE, CURSOR in my case logically would do same). Bellow article does not say(i think it should) avoid any looping, but exactly CURSOR (assumption was that, maybe WHILE is more preferable than CURSOR).

Avoid Using Cursors - SAP HANA SQLScript Reference - SAP Library

Why not be explicit about the expected output structure and avoid SELECT * FROM? It's only three columns anyway...

I definitely agree here.

But coming back to my real challenge, I have not found any answer in SCN for my question.

My requirement was to get this logic to Calculation view, this means I cannot use local temporary tables in it, because Calculation view is READ Only (even thou I do not understand why local temporary table, that will be deleted after end of session, can create any side effects and that is why cannot be used in Scripted Calculation view).

I do not see other option than pre aggregate data to db table with procedure, and build view on top of it, loosing all real-time capabilities...

It will be nice to have another solution, if someone has?

Thanks,

Markus

[lbreddemann · Accepted solution]

Hmm....

Ok, one by one then.

Do you think looping over all dates in the range is the best approach here?

I will be glad to hear another approach, but as i need to do calculation for each date separately, I do not see another one. Please kindly share if you have one.

Why do you need to loop then? What's wrong with a good old join?

Let's assume we continue using the M_TIME_DIMENSION table as the generator for our days, you could generate the list of all dates like this:

select date_sql from "_SYS_BI"."M_TIME_DIMENSION"
where date_sql
between add_months (current_date, -2) and current_date;

Now this gives you every date between the current_date and two months back.

You might of course pluck your "get me the first day of the month two months ago" logic in here, but for now, let's stick with this.

Next you want the counting being done for each day.

"Do something for every tuple (matched or not)  in my set" is colloquial for "OUTER JOIN" in SQL:

select date_sql, product, city, count(distinct customer) 
from customer_data 
    right outer join
         (select date_sql from "_SYS_BI"."M_TIME_DIMENSION" 
         where date_sql 
         between add_months (current_date, -2) and current_date) mydays
        on  date_sql between validfrom and validto
group by date_sql, product, city
order by city, date_sql, product;

There we go - for each date from our generator query, we get a count of the records that where valid for this day, grouped by city and product.

Why the WHILE loop? What's wrong with the FOR CURSOR LOOP?

After reading this article i decided to use WHILE ( WHILE, CURSOR in my case logically would do same). Bellow article does not say(i think it should) avoid any looping, but exactly CURSOR (assumption was that, maybe WHILE is more preferable than CURSOR).
Avoid Using Cursors - SAP HANA SQLScript Reference - SAP Library

The documentation clearly states to avoid looping over result sets (check the best practices) a.k.a. cursors.

However, in case you do need to loop over a cursor, it's better to use the syntax that is most clear about that.

Using the FOR CURSOR loop frees you from the possibility of bugs like wrong index counter handling, explicit cursor handling etc.

Performance wise, there is no performance difference I am aware of.

Alright, let us know how that all works for you.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for valuable inputs!

Outer join worked perfectly.

Best regards,

Markus