Monday 20 July 2015

SAP HANA: Achieving Set operations using Joins

Now we can have multiple discussions. As we know that achieving with UNION is better than JOINS. But again it depends on the volumes of the data and the place at which you are applying it.

As per our experiences, we also know that if we can achieve with the help of Attribute /Analytic Views it is better than doing the same with Calculation View Graphical / SQL. In this document i have tried to achieve with Attribute views as much as possible and while doing this i came to know about the Full Outer Join option through graphical models is not possible anymore.

These are 7 questions on which Jody has discussed,

Here are our Tables:
Table 1:

SAP HANA Join, SAP HANA

Table 2: 

SAP HANA Joins
Revision: 68

1) What are all the elements in both data sets?

SQL Solution: (Similar to UNION)

SELECT  
COALESCE(T1."ID",T2."ID") AS "ID",  
COALESCE(T1."NAME",T2."NAME") AS "NAME",  
COALESCE(T1."LAST_NAME",T2."LAST_NAME") AS "LAST_NAME",  
COALESCE(T1."ROLE_ID",T2."ROLE_ID")AS "ROLE_ID"  
FROM "EMPLOYEE" T1 FULL OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID ;  

Result:

HANA Certifications

Note: Full outer join option not coming for any of the Graphical HANA models.

2) Which elements are exclusive to SET 1?
SQL Solution: (MINUS/EXCEPT)

SELECT  
T1."ID",  
T1."NAME",  
T1."LAST_NAME",  
T1."ROLE_ID"  
from "EMPLOYEE" T1 LEFT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID  
WHERE T2.ID IS NULL;  

HANA Models:

1) Create a Attribute view with Left outer join on both the tables
2) Consume the Attribute view in the Graphical calculation view to apply filter "T2.ID" is NULL after the joining takes place. ( You cannot apply filter after joining using attribute View).

SAP HANA Tutorial, SAP HANA

Result:

SAP HANA Operations Joins

3) Which elements are exclusive to SET 2?

Similar to above mentioned solution, you need to select the fields from Table 2 and filter on T1.ID IS NULL.

4) Which elements are in both sets?

SQL Solution: (INTERSECTION)

SELECT  
T1."ID",  
T1."NAME",  
T1."LAST_NAME",  
T1."ROLE_ID"  
FROM "EMPLOYEE" T1 INNER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;  

Attribute View Solution:

SAP HANA: Achieving Set operations using Joins

Result: 

SAP HANA: Achieving Set operations using Joins


5) Which elements are in SET 1 (non-exclusive)?

SQL Solution:

SELECT  
T1."ID",  
T1."NAME",  
T1."LAST_NAME",  
T1."ROLE_ID"  
FROM "EMPLOYEE" T1 LEFT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;  

Attribute View Solution:

SAP HANA Operations

Result:

SAP HANA Certifications

6) Which elements are in SET 2 (non-exclusive)?

SQL Solution:

SELECT  
T2."ID",  
T2."NAME",  
T2."LAST_NAME",  
T2."ROLE_ID"  
FROM "EMPLOYEE" T1 RIGHT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;  

Attribute View Solution:

HANA Material, SAP HANA Certifications

Result:

SAP Operations Joins

7) Which elements are in SET 1 or SET 2?

SQL Solution: (Similar to UNION Without duplicates)

SELECT  
COALESCE(T1."ID",T2."ID") AS "ID",  
COALESCE(T1."NAME",T2."NAME") AS "NAME",  
COALESCE(T1."LAST_NAME",T2."LAST_NAME") AS "LAST_NAME",  
COALESCE(T1."ROLE_ID",T2."ROLE_ID")AS "ROLE_ID"  
FROM "EMPLOYEE" T1 FULL OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID  
WHERE T1.ID IS NULL OR T2.ID IS NULL ;  
Result:

SAP HANA Tutorial, SAP HANA


Source: scn.sap.com

No comments:

Post a Comment