Technical Articles
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 🙂