The Data foundation in Analytic view provides outer joins, referential joins and inner joins. These are all equi joins. The Logical view provides join capabilities with the attribute views with equi join and Temporal join (non equi join).
The SQL based Calc view provides all possibilities of equi and non equi joins.
Can you please provide more details on your requirement so that it can be properly discussed.
As you correctly mentioned the temporal join for attribute views is supported from SP05 onwards. Please note that it is supported for "Attribute views". So if you need some measures from the table which has from_date and to_date and need to join this with the transaction table, then you can perform that in the attribute view join. But then you may not be able to use any measures from the table containing from_date and to_date.
The 'between' join is also not currently available with the CE functions.
I would suggest the following:
1) You can create another data layer to store your 'derived' data with the from and to date logic
2) You can use SQL based calc view and use the query to derive the values based on the logic.
I tried using SQL Server syntax for "between join". I was hoping that "between join" was implemented in HANA, even though I could not find it in the documentation. It looks like it works.
I started with small sample table of contracts and a one-column table of calendar days. I was able to join them in SQL. This gave me the number of days covered by contract, even if contracts overlap.
SELECT "SERIAL_NUMBER", COUNT(DISTINCT cal."CalendarDays")CT
FROM "SCHEMANAME"."CONTRACT_TEST" contr
JOIN "SCHEMANAME"."CALENDAR_DAYS" cal
ON cal."CalendarDays" BETWEEN contr."ContractStart" AND contr."ContractEnd"
GROUP BY "SERIAL_NUMBER"
This won't work as an analytic view, but I think it would be OK as a SQLScript calculation view.
I have a similar issue where I'm trying to fetch data that lies between a range ( A left join).
For e.g., my Employee table has 2 columns named 'From' and 'To'.
And I want this joined with a table named 'Calendar' and join all dates that lie between this range.
While trying to fetch this data in a Calculation View, I was able to achieve it with a SQL syntax like Kristina mentioned, using the BETWEEN operator in my join.
Here's a part of the code:
JOIN "SOURCE_MANUAL_CONFIG"."DIM_FIRM_CALENDAR" C ON
C.CALENDARDATE BETWEEN E.DATEFROM AND E.DATETO
But, the issue is this deals with a large volume of data and the execution fails with an Error like: Column Store error; memory allocation failed.
Is there a possibility to achieve this in the CE language?
Or, is there any other workaround to achieve this?