Views, Sequences & Functions using SAP HANA SQL

Hello All

In hand, I am holding this blog, extension to my preceding post: https://blogs.sap.com/2019/11/29/table-operations-in-scp-hana-service/

This time, i showed the titled operations based out of SAP HANA SQL.

Source to write code: SAP HANA based Development Workbench: Editor

View: SAP HANA SQL dines multiple views. In common, a view is an virtual table based on the dynamic results returned in response to an SQL statement.

Initially, I started with two tables creations: Addresses, BusinessPartner.

Content maintained for Addresses & BusinessPartner  tables in SAP HANA based Development Workbench: Catalog

Addresses:

BusinessPartner:

Using these two tables, defined view BuyerView

namespace SQLHANA.hanaproject.epmmodel.tableschemacreations;

@Schema: 'ZSCHEMA_NEWQ'

context ZMASTERDETAILS{

@Catalog.tableType : #COLUMN 
    Entity Addresses {
        key  ADDRESSID: String(20);
        CITY: String(20);
        POSTALCODE: String(5);
        STREET: String(20);
        BUILDING: String(20);
        COUNTRY: String(3);
        REGION: String(4);
        ADDRESSTYPE: String(2);
        LATITUDE: Double;
        LONGITUDE: Double; 
    };

@Catalog.tableType : #COLUMN 
    Entity BusinessPartner {
        key  PARTNERID: String(5);
        PARTNERROLE: String(3); 
        EMAILADDRESS: String(30);
        PHONENUMBER: String(10);
        FAXNUMBER: String(10);
        ADDRESSES: Association to Addresses null;
        COMPANYNAME: String(80);
        CURRENCY: String(3);
    };
    
     define view BuyerView as SELECT from ZMASTERDETAILS.BusinessPartner {
        PARTNERID as "Id",
        EMAILADDRESS as "EmailAddress",
        COMPANYNAME as "CompanyName",
        ADDRESSES.CITY as "City",
        ADDRESSES.POSTALCODE as "PostalCode",
        ADDRESSES.STREET as "Street",
        ADDRESSES.BUILDING as "Building",
        ADDRESSES.COUNTRY as "Country",
        ADDRESSES.REGION as "Region"
   } where PARTNERROLE = 'RO1';
}

Output of view:

Sequence: A database sequence generates a serial list of unique numbers that you can use while transforming and moving data to between systems.

Syntax:

schema= "ZSCHEMA_NEWQ";
start_with= 1000000100;
maxvalue= 1999999999;
nomaxvalue=false;
minvalue= 1;
nominvalue=true;
cycles= false;
depends_on_table= "SQLHANA.hanaproject.epmmodel.tableschemacreations::ZMASTERDETAILS.BusinessPartner";

Sequence Display in SAP HANA based Development Workbench: Catalog:

Scalar Functions: We have multiple function types in SAP HANA SQL. For example, scalar functions are user-defined functions which accept multiple input parameters and result exactly one scalar value.

FUNCTION "ZSCHEMA_NEWQ"."SQLHANA.hanaproject.epmmodel.functions::apply_discount" (im_price decimal(15,2), 
                 im_discount decimal(15,2) ) 
	RETURNS result decimal(15,2)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
result := :im_price - ( :im_price * :im_discount );
END; 

Project Structure:

In further blogs, i will outline on Procedures too.

Thank you for your interest!!!

Keep posting me comments.

#EnhanceLearning

BR//Dhanasupriya Sidagam 🙂