Minus set operation in SAP HANA 2.0 Web IDE

[Subhendu]

Purpose:

Purpose of this blog is to demonstrate the use of “Minus” set operator in SAP HANA Modelling.

SAP HANA 1.0 modelling does not directly support the Minus operation between two data set, however in hana 2.0 SAP Web IDE modelling new feature added to support Minus operation between two data set.

What is Minus set operation?

The Minus operation combines results of two SELECT statements and return only those in the final result, which belongs to the first set of the result.



Use case/Business Scenario:

There are two superstore in our city- BigMarket and MegaMarket.

Let’s say Abhishek, Naim, Rohan, Ratan are customers of Bigmarket. And Abhishek, Naim, Nabanita, Ram are customers of MegaMarket.

Now I need to find who are the exclusive customer of BigMarket who only shops here and doesn’t visit other super market in the city.

In my case the answer is : Rohan, Ratan

Big Market data:



Mega Market Data:



Solution-1 ( Using SQL Query):

select CUSTOMER_NAME from "DB_45"."SuperStore.BigMarket"

minus

select CUSTOMER_NAME from "DB_45"."SuperStore.MegaMarket";

Query Output:



 

Solution-2 ( Using Graphical modelling):





Model Output:



Please feel free to comment on my post.

 

I've added all the SQL statement used in this blog below

context SuperStore {



    @Comment : 'BigMarket Customer Details'

    entity BigMarket  {

            KEY CUSTOMER_ID : Integer; 

            KEY CUSTOMER_NAME :  String(50);

    }

    technical configuration {

        column store;

    };

    

    @Comment : 'MegaMarket Customer Details'

    entity MegaMarket  {

            KEY CUSTOMER_ID : Integer; 

            KEY CUSTOMER_NAME :  String(50);

    }

    technical configuration {

        column store;

    };    



};





Insert into "DB_45"."SuperStore.BigMarket" values (1,'Abhishek' );

Insert into "DB_45"."SuperStore.BigMarket" values (2,'Naim' );

Insert into "DB_45"."SuperStore.BigMarket" values (3,'Rohan' );

Insert into "DB_45"."SuperStore.BigMarket" values (4,'Ratan' );



Insert into "DB_45"."SuperStore.MegaMarket" values (1,'Abhishek');

Insert into "DB_45"."SuperStore.MegaMarket" values (2,'Naim');

Insert into "DB_45"."SuperStore.MegaMarket" values (3,'Nabanita');

Insert into "DB_45"."SuperStore.MegaMarket" values (4,'Ram');

 

Conclusion:

Using HANA 2.0 WEB IDE we are getting lots of new advanced database feature like "Minus Set operation" which makes a Developers life easy 🙂