Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Subhendu
Explorer
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.
7 Comments
Labels in this area