Inequality join in Analytic view of SAP HANA

[Former Member]

I want to perform outer join between two tables using non-equal operator.

Can anyone suggest how can this be achieved using Analytic view of SAP HANA modeler.

[Former Member]

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.

[Former Member]

Between join you can do it graphically in HANA as this is the temporal join. That works well but is limited to using referential join. Problem is when you have an outer between join condition you cannot use temporal join in HANA.

[Former Member]

Hi Patrick,

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

WHERE E.DATETO<=TODAY_DATE

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?

Thanks,

Gautham

[Former Member]

Hi Varun

Did you manage to implement option 1? It would be interesting to know about the performance of option 1 as you would have created calculated columns as your 'derived data' which SAP would not recommend that they be used in join conditions.

Regards

Pat

[Former Member]

Thanks Ravi. I check going with option 1 and see how it goes.

[former_member184768]

Hi Varun,

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.

Regards,

Ravi

[Former Member]

Temporal Join feature is supported in SP5 release of HANA I guess.

I have two date columns in one table fromdate and todate. I want to join it with the date table based on that date falls between from and to date.

Can this be achieved through CE functions as well?

[former_member184768]

Hi Varun,

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.

Regards,

Ravi