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:
Table 2:
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:
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).
Result:
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:
Result:
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:
Result:
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:
Result:
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:
Source: scn.sap.com
No comments:
Post a Comment