Views, Sequences & Functions using SAP HANA SQL

[Dhanasupriya]

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 🙂