cancel
Showing results for 
Search instead for 
Did you mean: 

SAP HANA1SP12: how to implement NON EQUI-JOIN(Temporal join) on calculation view?

0 Kudos
919

SAP HANA Version: hana1sp12

There have a SQL statement is:

JOIN ON CREATE_DATE >= MODIFY_DATE

Because I have a lot of nodes in the old calculation view, and I want to implement new nodes will use non equi-join >= on the calculation view. After my search on the Internet, I saw people suggested to use 'temporal join'. But it is disable on calculation view at my hana1sp12 version. Then, I find some people have same issue(https://answers.sap.com/questions/133294/temporal-joins-option-is-disabled-in-hana-calculat.html). But no one know the exactly solution or answer about this issue.

Does someone could give suggestions for me if I need to implement in calculation view, or do I have work around methods to implement on calculation view?

Thank you.

J.K

Accepted Solutions (1)

Accepted Solutions (1)

KonradZaleski
Active Contributor
0 Kudos

Non equi-join are not supported for graphical views. As an option you can skip that condition in join node and then after Join, create a Projection with the filter expression CREATE_DATE >= MODIFY_DATE.

But from my perspective more accurate approach here is to create table function instead apply the logic in SQL.

0 Kudos

Thank for providing information to me.

former_member672402
Discoverer
0 Kudos

Thanks konrad for your very knowledgeable articles.

While stumbling upon ways to implement the Non equi-joins in SAP HANA studio landed here. I read your articles on Table functions and using them for Multi-Value input parameters in graphical views.

Here is my use case:

  1. We have a contract table (Base Table) that holds start and end date of contract. this contract period can vary between days, months to years. This is a huge table.
  2. For every such combination of dates, we need to look up the calendar table should split the contract period into weeks and return the data.
  3. If it's a monthly contract it can have 5 rows returned and 53 in case of yearly contract period.

Below solutions have been thought of assuming that a table function is created

Possible solutions:

Approach 1:Using start and end date as input parameter

  • If we can use start and end date from base table (Contracts) either as two separate parameters or combination of both as one parameter to call table function. (Since any combination of dates will have same data in calendar table)
  • Table function executes the code which returns all the weeks between those dates.
  • Is this approach possible ?, Is there a way in which we can join base table to table function projection and it takes join columns as an input parameter and requests a call to the function with this value for every record.

Approach 2: Table function built on values present in base table

  • Here, our table function code will intun be built up based on the base table.
  • Code execution here would be, For every contract dates, it is joined to calendar table returns table with a key (contract) to identify so that it can be used in calculation view.
  • Though this solution can be used, There are performance implications.
  • The base table has Billions of records and once a view starts executing both base table and table function are reading on the same table.
  • Furthermore, for this approach the no. of records returned from table function will be proportionate to base table while in first approach it will only hold around thousands of records.

Please let me know your thoughts. Thanks !!split-days-into-weeks.png

Answers (0)