8 Replies Latest reply: May 10, 2015 12:47 PM by Gautham Karthik S RSS

Inequality join in Analytic view of SAP HANA

Varun Gupta
Currently Being Moderated

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.

  • Re: Inequality join in Analytic view of SAP HANA
    Ravindra Channe
    Currently Being Moderated

    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

    • Re: Inequality join in Analytic view of SAP HANA
      Varun Gupta
      Currently Being Moderated

      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?

      • Re: Inequality join in Analytic view of SAP HANA
        Ravindra Channe
        Currently Being Moderated

        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

  • Inequality join in Analytic view of SAP HANA
    Varun Gupta
    Currently Being Moderated

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

  • Re: Inequality join in Analytic view of SAP HANA
    Pat Mataz
    Currently Being Moderated

    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

  • Re: Inequality join in Analytic view of SAP HANA
    Kristina Pereyra
    Currently Being Moderated

    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.

    • Re: Inequality join in Analytic view of SAP HANA
      Pat Mataz
      Currently Being Moderated

      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.

      • Re: Inequality join in Analytic view of SAP HANA
        Gautham Karthik S
        Currently Being Moderated

        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

Actions