Technical Articles
Non Equi join in SAP HANA 2.0 Web IDE
Purpose:
Purpose of this blog is to demonstrate the use of “Non Equi join” in SAP HANA Modelling. SAP HANA 1.0 modelling does not support the Non Equi join between two data set, however in hana 2.0 SAP Web IDE modelling new feature added to support Non Equi join.
What is Non Equi Join?
As per SAP Documentation Non-equi joins are joins whose join conditions use conditional operators other than equals.
A non equi join uses comparison operators like !=, >, <, >= and <= to query data from two data sources. Like a normal join, it allows you to specify the cardinality and a join type (Inner, Left Outer, Right Outer) to query data from two data sources. If multiple pairs of left and right columns are participating in join definition, each pair can have different operator.
Use case:
Non equi join can be used to detect duplicate data between tables. In This blog I’ve used non equi join to detect duplicate names in our employee dataset.
Table structure
Table Data:
In my example I will detect duplicate entries of First Name and Last Name of employees table.Here Combination of Business entity ID , First Name and last name makes the record unique, which is composite key in my Employee Table.
So the solution I used to detect the duplicate entry is that I self joined the Employee table with itself on the basis of First Name and Last Name where they are matching and Business Entity ID where they are mismatching.This is the place where Non – equi join comes into the picture.
Solution-1 ( Using SQL Query):
SELECT
t1."BUSINESS_ENTITY_ID",
t1."FIRST_NAME",
t1."LAST_NAME"
FROM "DB_45"."Employee.EMPLOYEE" t1 join
"DB_45"."Employee.EMPLOYEE" t2
on t1."FIRST_NAME" = t2."FIRST_NAME"
and t2."LAST_NAME" = t2."LAST_NAME"
and t1."BUSINESS_ENTITY_ID" <> t2."BUSINESS_ENTITY_ID";
Query output you can see as below which has detected duplicate
Solution-2 ( Using Graphical modelling):
Model Output:
SQL used in this blog can be find below:
context Employee {
@Comment : 'Employee Details'
entity EMPLOYEE {
KEY BUSINESS_ENTITY_ID : Integer;
KEY FIRST_NAME : String(50);
KEY LAST_NAME : String(50);
}
technical configuration {
column store;
};
};
delete from "DB_45"."Employee.EMPLOYEE";
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Subhas','Bose');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Rabindranath','Tagore');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Arabindo','Ghosh');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'John','Doe');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Subhendu','Ghanty');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Subhas','Bose');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Rabindranath','Tagore');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Subhendu','Ghanty');
select * from "DB_45"."Employee.EMPLOYEE";
Conclusion:
Using HANA 2.0 WEB IDE we are getting lots of new advanced database feature like “Non Equi Join” which makes a Developers life easy 🙂
Which can be used to remove duplicate as I’ve demonstrated above.
Please feel free to comment/ add any suggestion on this.
Nice Blog
Thanks Anupam Dash
You were great and everyone received so much from your experience and knowledge
Absolutely amazing, thank you for sharing your knowledge with me.
Informative
Very informative. Can you please share the summarized new technical features in HANA 2.0.
Thanks Harshith Tenneti . I will try to come up with SAP blog consolidating new features of HANA 2.0
Masih bingung mencari tempat bermain taruhan Daftar Situs Judi Slot Online Terpercaya yang aman dan nyaman serta menguntungkan? Tidak perlu khawatir, saat ini anda berada di situs yang tepat. Disini ASIAJOKER adalah salah satu pilihan situs judi slot terbaik dan terpercaya di Indonesia. Bahkan juga kami merupakan spesialis penyedia permainan taruhan judi slot gampang menang.