7 Replies Latest reply: Dec 14, 2013 4:25 PM by Pat Matambanadzo 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 Matambanadzo
    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.

Actions