Saturday, 11 January 2020

Minus set operation in SAP HANA 2.0 Web IDE

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.

SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

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:

SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

Mega Market Data:

SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

Solution-1 ( Using SQL Query):


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

Query Output:

SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

Solution-2 ( Using Graphical modelling):


SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

SAP HANA IDE, SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Certification, SAP HANA Prep, SAP HANA Online Exam

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');

No comments:

Post a Comment