Code to Data

ABAP to AMDP

Dr. Selva Lakshmanan

 Code To Data
ABAP TO AMDP

Dr. Selva Lakshmanan

Table of Contents
Introduction................................................................................................. 5
Code-To-Data .......................................................................................... 5
Open SQL ................................................................................................ 7
OpenSQL vs SQLScript ......................................................................... 9
AMDP Basics……………………………………………………………………13
Eclipse ABAP Development Tool………………………………………...13
Data Type……………………………………………………………….…....18
Date Function………………………………………………………………..23
String Function………………………………………………………………26
Array Processing……………………………………………………………33
Session Management………………………………………………………36
Database Transaction Management……………………………………..37
Error Handling……………………………………………………………….38
AMDP Enhancement………………………………………………………..46
Aggregation……………………………………………………………………..48
Subquery……………………………………………………………………..50
Set Operations……………………………………………………………….53
Grouping Set…………………………………………………………………54
Window Function……………………………………………………………56
Random Partition……………………………………………………………64
Advanced Topics……………………………………………………………….65
CDS Table Function………………………………………………………...65
AMDP Mesh…………………………………………………………………..67
Dynamic Programming…………………………………………………….69
Parallel Processing…………………………………………………………71
Series Generation…………………………………………………………..75
JSON/XML……………………………………………………………………78
Hierarchy……………………………………………………………………..84
Security……………………………………………………………………….89
Spatial Data……………………………………………………………….….92
Preface

The objective of the book is to offer how to handle the code-to-data

paradigm. The code-to-data paradigm offers you to use SAP HANA-
specific features such as column databases, parallel processing,
compression, and in-memory capability and to deliver your applications
with massive real-time data processing capabilities. In ABAP coding, the
developers are encouraged to keep loads away from the database layer and
use the application layer to handle massive data. SAP supports both top-
down and bottom-up code-to-data approaches. In this book, the bottom-up
code approach is only considered. The AMDP method is the main focus of
the book. The AMDP method creates the stored database procedure or
functions directly in the HANA database.

The book covers more declarative coding logic concepts. As an ABAP

developer, you are more familiar with imperative coding techniques and
use declarative logic on OpenSQL or native SQL calls. Currently, the
AMDP supports SQLScript coding only. SAP HANA database supports
two additional programming languages viz., L and R programming
languages. Declarative logic can formulate complex but still very high-
performance SQL queries. SQLScript supports both imperative and
declarative logic. Note that SAP HANA is highly effective with declarative
logic. In SQLScript, SAP recommends avoiding imperative coding.
The book is divided into four chapters. The first chapter focuses on the
introduction of the code-to-data paradigm and how SQLScript differs from
OpenSQL features. The second chapter provides the basics of AMDP with a
few commonly used scenarios that how can handle it in ABAP code and
AMDP code. The third chapter is highly focused on SQL aggregation
functionality. The powerful windows functions are discussed in detail. The
last chapter focuses on advanced topics such as Dynamic Programming,
CDS Table function, JSON/XML functions, Hierarchy functions, and spatial
functions. Each section is discussed with a few scenarios.

Hope that the book covers a wide range of topics and provides a good kick-
start on the code-to-data paradigm. This is a free book and you can
forward it to your friends and colleagues. Please feel free to send any
comments or feedback to me using the URL
https://docs.google.com/forms/d/e/1FAIpQLScPa4BtiuNinMFI_6ScsNyzay
7DRhaK0VniHRzsJbIhWpXpeg/viewform .
Introduction
From release NW7.4, SAP provides ABAP support for optimizing ABAP
applications running on SAP HANA. Transparent optimizations are
nothing but improvements in the ABAP stack so that your ABAP code can
benefit HANA advantages directly without any code adjustments. As an
application developer, you cannot see transparent optimizations. For
example, SAP improved the implementation of the SELECT FOR ENTRIES
statement. SAP uses a new data exchange protocol between the ABAP stack
and the database release 7.4 onwards. Using advanced OpenSQL and SAP
HANA native features, you can improve performance. The code-to-data
options can correlate to the potential gain in performance but they may also
increase the complexity of the code.

In this chapter, you can understand the code-to-paradigm and Top-Bottom,

and Bottom-Up approaches. According to SAP Help, OpenSQL is the
umbrella term for a subset of SQL realized using ABAP statements. In
every ABAP release, you can see quite changes in the OpenSQL statements
and usages. The chapter explains the features of OpenSQL. At the end of
the chapter, you can understand the features of OpenSQL, SQLScript, and
their differences.

Code-To-Data

With new SAP HANA capabilities, ABAP developers can accelerate

performance, extend the background job into interactive applications, and
innovate new processes and applications. By optimizing the code, the
runtime of the long-running background jobs can be reduced. It enables
extending the long-running background jobs into the online reports with
full drill-down capability. You can innovate new processes and
applications using new features and resources.
Code pushdown is the programming paradigm to push down the code
from the application layer to the database layer. The basic idea is that you
need to execute intensive and expensive computations in the database layer
so that you can use the computing power of HANA completely. A code
pushdown approach is a data-centric approach and the code is moved to
the database. Note that code push does not intend that you need to push all
calculations to the database.

Pre-ABAP 7.4, SAP provides very limited support for the code-pushdown
approach. SAP introduces the bottom-up approach on ABAP 7.4 SP02 and
the approach enables to consumption of HANA views and procedures in
the ABAP. With the release of ABAP7.4 SP05, SAP enables code pushdown
with ABAP for SAP HANA, the top-down approach, enabling the
developer to continue to work with the ABAP environment. The developer
can manage the modeling in the ABAP (application layer) and
corresponding objects are created in the HANA database.
SAP recommends the top-down approach. A CDS (Core Data Service)
View is an ABAP dictionary object that has two layers semantic layer and
an SQL view. CDS is a powerful tool to design virtual data models. You
need to define the CDS view in Eclipse (it has no classic editor in SAP GUI).
The CDS view carries more semantics than its SQL view. Note SQL view is
visible in the ABAP dictionary. AMDP (ABAP Managed Database
procedures) is a simple ABAP class method that contains native HANA
SQLScript. AMDP methods deploy database procedures and functions.

The CDS plays a vital role in the code pushdown concept. The main focus
of the books is how to use the AMDP methods in the code pushdown
concept. By implementing AMDP methods, you can see a big paradigm
shift in your programming. For simple SQL select statements, you can
ABAP OpenSQL statements instead of the AMDP method. The OpenSQL
statements are getting powerful and you can handle any complex query.
When you are moving the SQL statements in the database, you can see a
significant performance improvement. The simple rule is that do not use
simple select statements in the AMDP. The main purpose of the AMDP is
to handle multiple and complex queries.

Open SQL

ABAP OpenSQL is a database abstraction layer with SQL-like syntax in the

ABAP programming language. It is a common SQL semantic for all
databases supported by SAP. ABAP OpenSQL is very restrictive in
comparison with the SQL standards such as SQL92. With ABAP NW 7.40
SP05, SAP provides the extended OpenSQL with more features including
stricter syntax checks, more joins features, and supporting SQL expressions
and functions. Similarly, there are many changes from above NW 7.5
including the features like the FROM clause before the field list and INTO
clause at the very end of the SQL statement. The advanced OpenSQL
supports more of the standard SQL92. The maximum number of tables in
joins is increased to 50 and the maximum number of subqueries is increased
to 50. ABAP 7.40 OpenSQL is considered Enhanced OpenSQL and after
ABAP 7.5, it is known as Standardized OpenSQL. SAP simplifies the use of
HANA artifacts so that you can access SAP HANA objects using OpenSQL.
OpenSQL uses new syntax to support extended and standardized
OpenSQL. The ABAP supports older OpenSQL syntax also. You cannot
mix both OpenSQL with extended OpenSQL.
Common Table Expression

The Common table expression (CTE) is supported in OpenSQL from

ABAP7.51. The CTE is a powerful SQL construct that helps you to simplify
your complex SQL queries. The CTE is a subquery that is placed at the
beginning of a SELECT statement and uses a result set in the subsequent
SELECT statements. SAP introduces a new SQL keyword WITH and you
can define one or more CTEs and the subsequent SQL statements can use
CTE as a data source. The main advantages are:
• Easier to read since all subqueries are placed together
• Easier to understand compared with multiple SQL queries to build the
solution
• You can avoid FOR ALL ENTRIES statement
• You can use CTE where subqueries are not allowed within the SQL query
or you do not need to define subquery with the SQL statement

WITH
+vendors21 ( SELECT lifnr, name1 FROM LFA1 where ktokk = ‘Z021’ )
SELECT ekko.ebeln, ekko.bukrs, ekko.bstyp, ekko.bsart, ekko.lifnr,
+vendors21.name1
FROM ekko
INNER JOIN ON ekko.lifnr = +vendors21.lifnr
INTO TABLE @DATA( lt_tab ).
CTE is highly integrated with the CDS so that you can publish CDS
associations for subsequent queries of a WITH statement. CTE is always
challenging your code pushdown. You can still implement complex queries
directly in ABAP OpenSQL instead of the AMDP method. You can use
AMDP for any SQL statement or function which is not supported directly
by OpenSQL.

The following table lists all OpenSQL features and their availability.
OpenSQL Features Version
The right-hand side of join can be a join (other than table or From NW 7.4
view)
Non-Equi operators on ‘ON’ conditions (not just equal) From NW 7.4
WHERE condition of a left outer join can use any other From NW 7.4
operator (not just equal)
RIGHT OUTER JOIN From NW 7.4
ON Condition with IS or IS NOT NULL From NW 7.5
Support of UNION statement From NW 7.5
Common Table Expression From NW 7.51
Numeric Functions – ABS, CEIL, FLOOR, DIV, MOD From NW 7.4
Numeric Function – ROUND From NW 7.5
CAST function From NW 7.4
String operator && (allows more than two operands) From NW 7.4
String Functions – CONCAT, LPAD, LENGTH, LTRIM, From NW 7.5
REPLACE, RIGHT, RTRIM, SUBSTRING. Note that CONCAT
allows only two operands
Common Table Expression From NW 7.51
CROSS Join (Cartesian product) From NW 7.51

OpenSQL vs SQLScript

SAP ABAP transforms the OpenSQL statements into database-specific

statements. You can access only those database tables, views, and CDS
entities in OpenSQL. When you are moving your code to data, then you
need to know the native SQLScript SQL statements and understand the
syntax differences between them. The important syntax differences are
listed in the following table.
OpenSQL SQLScript
Field separator The comma (in extended), Comma
space (in classic OpenSQL)
Position of Fields clause Before or After FROM Before FROM clause
clause (you can use clause
 FIELDS in standardized
OpenSQL)
The separator between Tilde (~) Period (.)
THE table and the field
Schema Handling Implicit default schema (or Only default schema
define schema explicitly)
Client handling No implicit client handling Explicit client handling
Delimitate the number of UP TO … ROWS (with TOP … (in SELECT
rows FROM CLAUSE) CLAUSE)
Access to CDS views Only access to SQL view is Access to CDS view
supported. possible
CORRESPONDING Supported Not supported
FIELDS
FOR ALL ENTRIES Supported Not Supported.
APPEND INTO TABLE Supported Not supported
STATEMENT ends Period (.) Semi-colon (;)

SAP recommends breaking up complex SQL statements into multiple

simpler ones and allows you easier to comprehend the solution more.
Avoid the cursor and you can replace it with SQL statements. It enables
you opportunities for optimization and possibly parallel execution.
Dynamic SQL is slow and it requires compiling time checks and query
optimization at every invocation of the statement. Another problem with
dynamic SQL is security issues because the SQL is constructed without
proper checks.

Refactoring SQL is to make sure that the query is written better or in a

position to be made better later on. Refactoring is the process of
restructuring code without changing its functionality. The pretty printer is
one of the options provided by SAP GUI. There is no direct pretty printer
on Eclipse but a pretty printer extension is available.

The main purpose of SQLScript is to embed data-intensive application logic

into the database that enables developers to push data-intensive logic into
the database. SQLScript provides multiple enhancements to the standard
SQL with logical programming concepts including procedures, functions,
and anonymous blocks as logical containers. It enables you to formulate
complex declarative logic. Declarative logic is used for the efficient
execution of data-intensive computations. SQLScript uses Orchestration
and imperative logic to implement data-flow and control-flow logics such
as IF/ELSE and FOR loops. SAP HANA optimizes declarative queries and
SAP recommends avoiding imperative statements.
SAP HANA query execution engines are responsible for different engine
types. The engine types include Row Engine, Column Engine, Join Engine,
OLAP Engine, and Calculation Engine. When you read data from a row-
store table, then the row engine is carried out. In contrast, the column
engine is carried when you read data from a column-store table. SAP
recommends avoiding switching between column and row store engines.
When your query retrieves data from the row store and the column store,
then a switchover between two engines takes place and costs additional
runtime. Restrict your data volumes as early as possible. Your runtime
depends on the number of data records and the number of columns.

You can create proxy objects to consume SAP HANA objects. There are two
important proxy objects viz., the external views and database procedure
proxies. ADT eclipse is required to create an external view and a database
procedure proxy. The external view is a data dictionary view which refers
to the HANA database view. The external views are just proxies for the
HANA views. You can view only the field names only not any join
conditions. Note that the SAP HANA view interface requires manual
synchronization. You need to handle the syntax error (missing the HANA
view) and runtime error when does not match the external view. You
cannot trace the selection criteria on the ABAP trace.

Before ABAP 7.4 SP05, the AMDP is not supported and SAP provides the
option to create a database procedure proxy that refers to the HANA
database procedure. Note that all parameters of the procedure proxy are
mandatory. Like the external view, the database procedure proxy requires
manual synchronization and possible potential runtime errors. And there is
no extensibility for the proxy procedure like for AMDPs.
In a relational database, the operations act always on a complete set of
records. Some applications or requirements cannot work always effectively
with the entire result set as a unit. The database cursor enables you to
work with one row or a small block of rows at a time. Like ABAP,
SQLScript supports the database cursor. In SQLScript, you can
parameterize the cursor with input parameters. SAP recommends avoiding
cursors in SQLScript code.
ABAP database connectivity (ADBC) provides ABAP APIs for native SQL
calls. You can directly access the native SQL statements. The object-based
flexible ADBC technique supports error handling. One of the main ADBC
classes, CL_SQL_STATEMENT enables executing SQL (HANA SQLScript)
directly. You can see the implementation of the JSON document store
example. ADBC is purely based on dynamic SQL statements and SAP
recommends using ADBC only for short native SQL calls otherwise, use the
AMDP.
AMDP Basics
SAP released the AMDP functionality in Release 7.40 and SP05 allowed
developers to create the AMDP procedure. AMDP is used as a top-down
approach to create and call stored procedures on the SAP HANA database.
Using the AMDP technique, you can perform lifecycle management of
database procedures/functions on the ABAP application server. AMDP
technique enables you to edit and manage the procedures, syntax check,
and automatic deployment of stored procedures.

The chapter intends to explain the AMDP basics such as data types, date
and string functions, array processing, and session management. The string
functions include concatenation, regular expression, map function, and
Unicode normalization. SQLScript built-in libraries are discussed with the
scenarios. Also, the chapter discusses the full-text search features. The
SQLScript error handling process and AMDP enhancements are discussed
in detail. At the end of the chapter, you can provide the basic functions of
the AMDP.

Eclipse ABAP Development Tool

You cannot develop the AMDP class using the ABAP workbench but you
can view the AMDP class in SAP GUI. You need to use the ABAP
development tool (ADT) Eclipse for AMDP development. Eclipse provides
a brand-new ABAP development experience on top of the Eclipse platform.
After long years of SAP GUI usage, you may feel difficulty working with
the adaption of Eclipse. With Eclipse, you benefit from the usability, speed,
and flexibility of Eclipse along with proven ABAP workbench features.
Eclipse IDE (integrated development environment) is a modern IDE and is a
widely used development environment. Some advantages of the Eclipse
are:
• Eclipse framework supports multiple programming languages
programming.
• Connect to multiple ABAP systems so you can work parallelly and switch
between systems easily.
• Eclipse provides session failover (that reduces the impact of lost
connections) compare with the timeout of the SAP GUI issue.
 • You can work offline. You can cache your local development and it can be
synchronized when there is a connection with the server. So, you do not
lose your development work.
• Quick Fix option (Ctrl-1) that enables the automated adaptation of custom
code
• Navigation history (Alt+Left or Right) enables you to jump between
different objects.
• The latest ABAP debugger is highly integrated with Eclipse debug
framework.

Before your AMDP development, you can verify whether your current
database supports AMDP procedures using the class method
CL_ABAP_DBFEATURES=>USE_FEATURES. If the method does not
return true, then you cannot develop AMDP code in your system.

IF NOT cl_abap_dbfeatures=>use_features(
EXPORTING
requested_features =
VALUE #( ( cl_abap_dbfeatures=>call_amdp_method ) ) ).
cl_demo_output=>display(
`Current database system does not support AMDP procedures` ).
RETURN.
ENDIF.

AMDP objects are created as methods of ABAP class with prerequisites in

the class definition.
• The class must implement interface IF_AMDP_MARKER_HDB.
• Parameters must be scalar types or table types with scalar columns and
must be passed by value (no call by reference).
• For parameters with table types, the line type has to consist of elementary
components. AMDP does not support generic data types (such as TYPE
TABLE or REF TO DATA) and nested tables in the line type.
• Methods must be marked with BY DATABASE PROCEDURE and
language HDB. When you use any database sources in the AMDP method,
then it must be specified with USING clause. The database sources include
tables, views, and other AMDP methods.
• Methods support almost full SQLScript reference except for the CE function
CE_COLUMN_TABLE.

CLASS ZCL_DEMO_AMDP definition

public
 final
create public.

PUBLIC SECTION.
interfaces IF_AMDP_MARKER_HDB .
methods AMDP_INVENTORY
importing
value(IV_PAYMENT_DATE) type D
value(IV_CLNT) type SY-MANDT
exporting
value(ET_INVOICE_HEADER) type TT_INVOICE_HEADER
raising
CX_AMDP_ERROR.

PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS CL_DEMO_AMDP_VS_OPEN_SQL IMPLEMENTATION.

METHOD amdp BY DATABASE PROCEDURE FOR HDB

LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING LINK LINP.
…
….
ENDMETHOD.
ENDCLASS.

You can invoke the AMDP method like any other ABAP method. The
AMDP methods are executed like static methods irrespective of method
definition (instance or static). When you execute the AMDP method, the
ABAP kernel calls corresponding database procedures. The method body
is implemented using SQLScript code. SAP strongly recommends using the
character set 7-bit-ASCII when you are implementing an AMDP method.
AMDP method creates a database procedure with a mark BY DATABASE
PROCEDURE in the method implementation. Note that THE AMDP
framework does not require any additional SAP HANA database user to
create database procedures. It uses your ABAP user. THE AMDP creates a
database function with the mark BY DATABASE FUNCTION. You need to
understand the user scalar and table-defined functions. The scalar user-
defined function (UDF) returns the scalar value(s). Table user-defined
returns table of contents. The scalar UDF can return multiple scalar values
but the table UDF can return only one table-typed variable.
The AMDP function uses RETURNING parameter instead of EXPORTING
parameters. The main advantage of the table function is that you can use it
as a data source in other AMDP methods. Also, you can use it in the map
merge and map-reduce function to process the table UDF with parallel
processing. The map-merge and map-reduce are discussed in the parallel
processing section.

The AMDP class is a client-independent object and it requires handling the

client manually on any client-dependent data (with MANDT or client in the
table). So, when your SAP instances have more than one instance then you
must handle the client in your SQL statement. You can use the function
SESSION_CONTEXT to get your client number. The AMDP method is
based on SQLScript and you need to understand the basic SQLScript
coding. Each SQLScript statement is ended with ‘;’. You can comment
SQLScript using ‘*’ at the beginning of a line (same as ABAP) and ‘--' at any
part of a line (‘”’ as in ABAP).

Debugging

The main advantage is that SQLScript debugging is integrated into ABAP

debugging. Eclipse ADT allows toggling breakpoints. You need to activate
the AMDP debugger so that you can debug the lines of the AMDP method.
You can view the table type variables in the SQLScript code.
You can manage the debugging of ABAP development objects using the
debug perspective in the Eclipse ADT. You can double-click on a variable
in the source code editor shows you the value and attributes of the variable
in the Variables view. You can see the system variables in the following
screen. You can get the values of variables by clicking the variables option
on the debug screen.

The following system variables are available in AMDP debugging screen.

System Variable Description
::CURRENT_OBJECT_NAME Current object (Program) name
::CURRENT_OBJECT_SCHEMA Current schema
::ROW_COUNT Row count when any SQL statements are
executed
::CURRENT_LINE_NUMBER Return the line number of the current
statement
::SQL_ERROR_CODE Returns the current SQL error code
::SQL_ERROR_MESSAGE Returns the current SQL error message

DUMMY Table

You can see a common statement FROM DUMMY in the SQLScript. The
DUMMY is a public synonym table and it has one row and one column.
When you use the SELECT FROM DUMMY, it always returns a single
record. You can use the DUMMY to mock the data or populate the table-
typed variable. The DUMMY is not considered a data source so you cannot
define the DUMMY as the data source in USING clause of the AMDP
method. The following code snippet is just to demonstrate the AMDP code
to populate the internal table type using the DUMMY. Note that SQLScript
does not support the range field in the IN operator.

ABAP Code:
DATA lt_tab TYPE RANGE OF i.
lt_tab = VALUE #( sign = 'I' option = 'BT' ( low = 1 high = 10 )
 ( low = 21 high = 30 )
( low = 41 high = 50 )
option = 'GE' ( low = 61 ) ).
AMDP Code:
lt_tab = SELECT ‘I’ AS sign, ‘BT’ as option, cast(1 as int) as low, cast(10 as int) as high
from DUMMY
UNION SELECT ‘I’ AS sign, ‘BT’ as option, cast(21 as int) as low, cast(30 as int) as
high from DUMMY
UNION SELECT ‘I’ AS sign, ‘BT’ as option, cast(41 as int) as low, cast(50 as int) as
high from DUMMY
UNION SELECT ‘I’ AS sign, ‘GE’ as option, cast(61 as int) as low, null as high from
DUMMY;

--or

DECLARE lt_tab TABLE ( sign NVARCHAR(1), option VARCHAR(2), low integer,

high integer );
:lt_tab.INSERT((‘I’,’BT’,1,10));
:lt_tab.INSERT((‘I’,’BT’,21,30));
:lt_tab.INSERT((‘I’,’BT’,41,50));
:lt_tab.INSERT((‘I’,’GE’,61,null));

Data Type

The data type defines the characteristics of the data value. SQLScript
supports both scalar and composite data types. The scalar type allows you
to store exactly one value whereas the composite data type refers to table-
type data types. In SQLScript, there is a special value NULL included in
every data type. The NULL indicates the absence of the data value.
SQLScripts follows the list of supported data types.

Classification Data Types

Binary Types VARBINARY
Boolean Types BOOLEAN
Character String Types VARCHAR, NVARCHAR, ALPHANUM,
SHORTTEXT
Datetime Types DATE, TIME, SECONDATE, TIMESTAMP
Large Object Types BLOB, CLOB, NCLOB, TEXT
Spatial Types ST_GEOMETRY, ST_POINT

You can see the list of ABAP data types and their equivalent SQLScript data
types.
 HANA Data Type ABAP Data Type Description
SMALLINT INT2 2-Byte integer
INTEGER INT4 4-Byte integer
DECIMAL DEC Packed number
SMALLDECIMAL DEC Packed number
FLOAT FLTP Binary floating point number
VARCHAR CHAR Character string
NVARCHAR CHAR Unicode character string
VARBINARY RAW Byte string
BLOB RAWSTRING Byte string
CLOB STRING Character string
NCLOB STRING Unicode character string

SQLScript allows you to create table type explicitly via CREATE TYPE. But,
the AMDP method does not support the CREATE TYPE statement.
SQLScript provides a CAST function (supported in the OpenSQL from
NW7.4) that enables you to convert the value of an expression into a
supplied data type. Also, there are multiple HANA conversion functions to
support both implicit and explicit data type conversions. The few data type
conversion functions include TO_DATE, TO_CHAR, TO_INT,
TO_NUMBER, TO_DECIMAL, etc.

Variables

You can define local variables using the DECLARE statement. By default,
the scalar variables are initialized with NULL. You can assign a default
value using the assignment operator ‘=’ or the keyword DEFAULT.
SQLScript provides you the option to initialize with the declaration.
Variable names are case insensitive and they support alphanumeric
characters, #, $, and _. Table variables are variables with a reference to the
tabular data structure. You can directly assign the table variables from the
select statement, TABLE keyword, or array function (that converts into table
type). You can make a variable into a constant by adding the CONSTANT
keyword after the variable (applicable for both scalar and table variables).
Let's start with a simple scenario that you are required to append lines of an
internal table into another internal table with the same data type. You can
see both the ABAP code and the AMDP code.

ABAP Code:
APPEND LINES OF lt_tab1 TO lt_tab2.

AMDP Code:
:lt_tab1 = :lt_tab1 UNION :lt_tab2;
-- or you can use SELECT statement
lt_tab1 = SELECT * from :lt_tab1
UNION
SELECT * FROM :lt_tab2;

SQLScript also provides an option to declare a variable without explicit

data type. The AUTO keyword determines the data type from its
assignment. The default value is mandatory when you declare the AUTO
variable. The AUTO function has several limitations and please use it with
care.
DECLARE lv_var1 AUTO = 1.0;
DECLARE lv_arr1 AUTO = array(1, 2);
DECLARE lt_tab1 AUTO = select 1 as x from dummy; -- or
lt_tab1 = select 1 as x from dummy;

The scope of the variable is based on where they are defined. You can
define the local variables inside BEGIN-END/LOOP/WHILE/FOR/IF-
ELSE control structures. The scope of the variables is only with the control
structures.
DECLARE lv_a INT = 1;
BEGIN
DECLARE lv_a INT = 2; -- local variable a with value 2
BEGIN
DECLARE lv_a INT;
Lv_a = 3; -- value 3 and loses its scope once it is out of the block
END;
ev_out = lv_a; -- value is 2
END;
ev_out1 = lv_a; -- value is 1

SQLScript provides an option to declare the sorted table variables to

provide efficient access to data records. The table variables are sorted by a
search key which is specified in the data declaration. You can use the
SEARCH keyword to search the sorted table variables. The sorted key is
very restrictive because you cannot influence the sorting such as ascending
or descending order fields. The table is automatically sorted by the search
key.

DECLARE lt_po1 TABLE(ebeln VARCHAR(10), bstnk VARCHAR(18), bmwtr

DECIMAL(13,2) ) SEARCH KEY(bstnk);

DECLARE lv_search_result int;

Lv_search_result = :lt_po1.SEARCH(bstnk, ‘somevalue’);
You can see commonly used ABAP system variables and their
corresponding AMDP SQLScript system variables.

ABAP SQL Script

sy-dbcnt ROW_COUNT – Returns how many rows are affected in
DCL as well as the number of rows selected in DML
sy-name CURRENT_USER, SESSION_USER
sy-datum CURRENT_DATE, NOW
sy-mandt SESSION_CONTEXT( ‘CLIENT’ )
sy-tabix CURRENT_LINE_NUMBER
sy-uzeit CURRENT_TIMESTAMP

Note that the UNION ALL statement does not remove duplicate records.
Whereas, the UNION statement implies the distinct semantic and removes
the duplicate record. Note that UNION and UNION ALL require the
records in the same order and a compatible type when you try to union two
different SELECT statements. The code snippet has the assumption that
both internal tables are the same data-typed.

In ABAP, you can declare the variables using the DATA statement. The
SQLScript uses the DECLARE statement to do the same. The scope of the
SQL Script variable is global when you declared the variable in the main
body of the procedure or function. Also, you can declare a local variable in
the anonymous block and the scope of the variable is within the block only.
You can define an anonymous block of code within BEGIN and END.

Anonymous Block

You can use the anonymous block like the local procedure function style by
passing the parameters. The anonymous block is an executable DML
statement with imperative or declarative statements. See the sample code
snippet.
DO (IN iv_input1 NVARCHAR(24)=> 'A',OUT lt_out TABLE (col1 INT, col2
INT ) => ?)
BEGIN
T1 = SELECT I, 10 AS J FROM TAB where z = :lv_input1;
T2 = SELECT I, 20 AS K FROM TAB where z = : lv_input1;
T3 = SELECT J, K FROM :T1 as a, :T2 as b WHERE a.I = b.I;
lt_out = SELECT * FROM :T3;
END;
You can see the table Dummy frequently in SQLScript. The dummy is a
row-based table and it can be used to return a single row. The following
example demonstrates to get the current user into the variable lv_user.

DECLARE LV_USER VARCHAR(20);

SELECT CURRENT_USER FROM DUMMY INTO lv_user;
or
DECLARE LV_USER VARCHAR(20) := CURRENT_USER;

Also, you can return the value as table type with the selected information.
This is very useful to initialize the table record. The following sample code
creates a two-column record into the table lt_record variable.

lt_record = SELECT ‘4300020199’ as VBELN, ‘Info’ as ZCOL1 FROM DUMMY;

The internal table lt_record will have a single record with two fields VBELN
and ZCOL1.

Currency conversion and UOM conversion

The ABAP function module CURRENCY_CONVERSION is used to convert

the values between currencies. SQLScript provides the
CURRENCY_CONVERSION function to calculate values in a different
currency. The prerequisite is all currency conversion tables TCURV,
TCURX, TCURN, TCURR, and TCURF must be available in the HANA
database. The SAP HANA DB function CONVERT_CURRENCY only
supports columns XINVR, BWAER, XBWRL, and XEURO from customizing
table TCURV. Check OSS notes 2792149 for further details.

METHOD convert BY DATABASE PROCEDURE FOR HDB

LANGUAGE SQLSCRIPT.
et_curr = SELECT CONVERT_CURRENCY(amount=> price,
"SOURCE_UNIT" => 'USD' ,
"SCHEMA" => CURRENT_SCHEMA,
"TARGET_UNIT" => 'EUR',
"REFERENCE_DATE" => refdate,
"ERROR_HANDLING"=>'set to null',
"CLIENT" => '000') as cprice
FROM :it_input;
ENDMETHOD

Similarly, SQLScript provides the CONVERT_UNIT function to convert

between the units of measure.
 SELECT CONVERT_UNIT("QUANTITY"=>quant
, "SOURCE_UNIT" =>source_unit
, "SCHEMA" => CURRENT_SCHEMA
, "DATABASE" => 'HDB'
, "TARGET_UNIT" => target_unit
, "ERROR_HANDLING"=>'set to null'
, "CLIENT" => '000') AS converted
FROM :lt_input;

GUID
A Globally Unique Identifier (GUID) is a system-generated unique key to
identify the business objects. The GUID is highly used in CRM, SRM, and
new business suite products. used as the key for unique identification. The
GUID is a RAW data type with three formats GUID16, GUID22, and
GUID32. You can use the function module GUID_CREATE to generate
GUID16, GUID22, or GUID32. In SQLScript, you can generate using the
NEWUID or SYSUUID function. The NEWUID function returns
VARBINARY(16). The SYSUUID function returns VARBINARY(32). See
the following code snippet to generate GUID.

DECLARE lv_uuid16 VARBINARY( 16 );

DECLARE lv_uuid32 VARBINARY( 32 );

SELECT newuid( ) INTO lv_uuid16 from dummy;

SELECT sysuuid INTO lv_uuid32 from dummy;

Date Function

The date function is one of the important to cover in code pushdown.

SQLScript provides multiple date conversion functions and the few
functions are listed in the following table.

Function Comments
TO_DATS Convert the date into ABAP date string
YYYYMMDD. Use this method to convert into
ABAP date format (sy-datum)
TO_DATE Convert the date into a required output format
ADD_DAYS Add days to the ABAP date
ADD_MONTHS Add months to the ABAP date
ADD_YEARS Add Years to ABAP date
 ADD_SECONDS Add seconds to date/time
NANO100_BETWEEN Returns the difference between two dates in
nanoseconds (0.1 microseconds).
EXTRACT Get date components like a year, month, and day

Scenario: You need to calculate the last day of the month which is 2 months
from today.

DECLARE lv_date DATE;

SELECT ADD_MONTHS_LAST (TO_DATE (CURRENT_DATE, 'YYYY-MM-DD'),
1) "add months last"
FROM DUMMY INTO lv_date;

You can extract the year, month, day, hour, minute, and seconds from the
date variable.

SELECT EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) as

Year,
EXTRACT (MONTH FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) as
Month,
EXTRACT (DAY FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) as Day
FROM DUMMY;

Scenario: The fiscal year is a financial year used by the company. For
example, the US federal uses the fiscal year variant (V9) that starts the
period in October and ends in September. In ABAP, you can get the fiscal
year and period using the function module
DATE_TO_PERIOD_CONVERT. See the following code snippet to get the
fiscal year and period for a given date and fiscal year variant.

SELECT ( CASE reljr WHEN 0 THEN YEAR(:lv_date) ELSE YEAR(:lv_date)+1

END),
POPER
INTO ev_fyear, ev_poper
FROM T009B
WHERE mandt = SESSION_CONTEXT( ‘CLIENT’ )
AND monat = MONTH(:lv_date)
AND periv = ‘V9’; -- can be taken from company code also

Scenario: Consider the asset aging report to list out all assets with age
(based on the reference date) and age category. You can see a continuation
of this summarized aging report in the window function section. The
sample code snippet demonstrates the date function to calculate the age and
age category. The base table is ANLA and it is obsolete in the S/4 HANA.
AMDP Code:

METHOD asset_aging
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS
READ-ONLY
USING anla anlc anlz.

et_out =
SELECT a.bukrs as bukrs,
DAYS_BETWEEN( a.zugdt, :iv_bdate ) as age,
( CASE WHEN DAYS_BETWEEN( a.zugdt, :iv_bdate ) > 365 THEN 'Over
365 days'
WHEN DAYS_BETWEEN( a.zugdt, :iv_bdate ) BETWEEN 181 AND 364
THEN 'Bet. 180 & 365'
WHEN DAYS_BETWEEN( a.zugdt, :iv_bdate ) BETWEEN 91 AND 180
THEN 'Bet. 90 & 180'
ELSE 'Within 90 days' END ) as age_cat,
a.posnr as posnr, z.gsber as gsber, a.anln1 as anln1, a.anln2 as anln2, z.kostl
as kostl,
a.zugdt, c.answl as acqval, 1 as count,
c.kansw, c.answl, c.gjahr
FROM anla AS a
JOIN anlc AS c
ON a.bukrs = c.bukrs AND
a.anln1 = c.anln1 AND
a.anln2 = c.anln2
JOIN anlz AS z
ON a.bukrs = z.bukrs AND
a.anln1 = z.anln1 AND
a.anln2 = z.anln2 ;
et_out = APPLY_FILTER( :et_out, :iv_where ); -- Filter the results based on
where clause
ENDMETHOD.

Time Zone

In ABAP, you can use the CONVERT statement to convert the date or time
between the local time zone and the specified time zone. The time zones
must be defined in the table TTZZ. The current time zone is stored in the
table TTZCU and you can get the current time zone using the FM
GET_SYSTEM_TIMEZONE. The following ABAP code snippet converts
the timestamp to EST time zone.

CONVERT TIME STAMP lv_time_stamp TIME ZONE ‘EST’

INTO DATE DATA(lv_dat) TIME DATA(lv_tim) .
SAPScript provides two time zone functions to convert the time between
time zones. The UTCTOLOCAL function converts time from UTC to the
local time zone. You can use the function LOCAL2UTC to convert time
from the local time zone to UTC.

DECLARE LV_LOCAL TIMESTAMP;

DECLARE LV_UTC TIMESTAMP;
SELECT TO_VARCHAR(UTCTOLOCAL(:IV_TIMESTAMP, 'UTC' )) INTO
LV_LOCAL FROM DUMMY;

SELECT LOCALTOUTC (TO_TIMESTAMP(:IV_TIMESTAMP, 'YYYY-MM-DD

HH24:MI:SS'), 'EST') INTO LV_UTC FROM DUMMY;

String Function

SQLScript string functions are scalar functions that manipulate input string
and output the string or numeric value based on the function. Like ABAP,
SAP SQLScript provides multiple string functions. String expression and
string concatenation are now a part of OpenSQL. The constant string can be
enclosed in a single quotation mark. You can assign the Unicode string
with the prefix N (uppercase N) for example N’unicode’.

Scenario: The conversion exit function is a commonly used conversion

routine that converts between internal data and external data. For example,
vendor 101 is stored in LFA1 as 0000000101. SQL HANA SQLScript
supports a rich set of string functions. Note that most string functions are
available in the OpenSQL from NW 7.5. SQLScript provides two functions
to convert the alphanumeric and you can see it in the following code
snippet.

AMDP Code:
DECLARE lv_lifnr VARCHAR(10);
SELECT ABAP_ALPHANUM(:lifnr, 10) FROM DUMMY INTO lv_lifnr;
-- or
SELECT LPAD(:lifnr, 10, ‘0’)
FROM DUMMY INTO lv_lifnr;

SQLScript provides another function ABAP_NUMC that converts the input

string to a specified length string with only numerical values.
 DECLARE lv_numc VARCHAR(10);
SELECT ABAP_NUMC(12, 3) FROM DUMMY INTO lv_numc; -- returns 012
SELECT ABAP_NUMC(1234, 3) FROM DUMMY INTO lv_numc; -- returns 234
SELECT ABAP_NUMC(-123.6, 3) FROM DUMMY INTO lv_numc;
-- returns 124 rounded and no sign

Concatenation

In SQLScript queries, you can concatenate up to 255 terms into a single

expression. But in the variable assignment, there is no hard limit on the
number of terms. The limitation of concatenated expression cannot be
longer than 5000 characters. The function CONCAT returns a combined
string for given two strings. Note that when you concatenate with a NULL
string, then you will get the result as the NULL. You can use the function
CONCAT_NAZ to return the combined non-null string. When one of the
values is NULL then it returns the non-NULL value. If both strings are
NULL, then it returns NULL.

Scenario: Assume that there is an internal table with three columns viz.,
GRP (integer), ID (integer), and ZFIELD (string). You need to concatenate
all zfield into one string delimited by a comma for each unique group.
Also, the order of concatenation is based on the value of id (in ascending
order). The sample data and expected results are in the following tables.

GRP ID ZFIELD GRP Output

1 1 Str1 1 Str1,Str3,Str2
1 3 Str2 2 Str4,Str5
1 2 Str3
2 8 Str4
2 7 Str5

ABAP Code:
LOOP AT it_tab INTO DATA(lv_tab)
GROUP BY ( grp = lv_tab-grp) ASCENDING ASSIGNING FIELD-
SYMBOL(<fs_grp>).
CLEAR: lv_str.
LOOP AT GROUP <fs_grp> ASSIGNING FIELD-SYMBOL(<fs_grp>).
CONCATENATE lv_str <fs_grp>-zfield CONCATENATED by ‘,’;
ENDLOOP.
lv_tab1-grp = lv_tab-grp.
DATA(lv_len) = STRLEN( lv_str ).
Lv_tab1-output = lv_str(lv_str-1).
APPEND lv_tab1 TO lt_tab1.
ENDLOOP.
AMDP Code: The solution is based on the string aggregate function.

et_out = SELECT grp, STRING_AGGR(zfield, ‘,’ ORDER BY id) as output

FROM :it_tab
GROUP BY grp;

Map Function

SQLScript provides the MAP function that searches the expression within a
set of values and returns a matched result or default result. This is a very
handy function that helps you replace a simple length CASE statement. For
example, you need the month name for the period with the fiscal year in the
accounting invoice document. See the following code snippet (for the fiscal
year starting from January) that uses the MAP function instead of the CASE
WHEN statement. The CONCAT function is used in the code snippet.
Instead of CONCAT, you can use a single row/column subquery.

et_out = SELECT bukrs, belnr, gjahr,

MAP(monat, 1, CONCAT(‘Jan ’, gjahr), 2, CONCAT(‘Feb ’, gjahr),
3, CONCAT(‘Mar ’, gjahr), 4, CONCAT(‘Apr ’, gjahr),
5, CONCAT(‘May ’, gjahr), 6, CONCAT(‘Jun ’, gjahr),
7, CONCAT(‘Jul ’, gjahr), 8, CONCAT(‘Aug ’, gjahr),
9, CONCAT(‘Sep ’, gjahr), 10, CONCAT(‘Oct ’, gjahr),
11, CONCAT(‘Nov ’, gjahr), 12, CONCAT(‘Dec ’, gjahr) ) as MonYear
FROM bkpf
WHERE BLART = ‘RE’ -- and additional conditions;

Regular Expression

Pattern matching is one of the significant SQL features that enables you to
search for patterns in data. You can use the regular expression to do pattern
matching. The regular expression is a generalized expression that is used
to match patterns with various sequences of characters. It is commonly
called RegEx. The pattern can be one or more character literals, operators, or
constructs. The search pattern is based on Perl Compatible Regular
Expression (PCRE). ABAP supports the regular expression in the FIND,
FIND ALL OCCURRENCES, and REPLACE ALL OCCURRENCE
statements. Also, you can use the ABAP class CL_ABAP_REGEX.
RegEx functionality is supported in multiple SQL functions and predicates
like FIND, REPLACE, LIKE, and STRING. The functions are:
 • LOCATE_REGEXPR – The locate function returns the position of a
substring within a string. The LOCATE_REGEXPR function searches a
string for a regular expression pattern and returns an integer indicating the
beginning position, or the ending position plus 1, of one occurrence of the
matched substring.
• OCCURENCES_REGEXPR – The function returns the number of matches
of a regular expression search within a string.
• REPLACE_REGEXPR – The function searches the regular expression and
replaces patterns with the replacement.
• SUBSTRING_REGEXPR – It searches a string for a regular expression
pattern and returns one occurrence of the matching substring.
• LIKE_REGEXPR – The predicate performs the regular expression matching.
You can use the predicate in the WHERE clause or any expression.

Scenario: Consider the scenario, you have a custom table with the
customer’s name, and phone number (character string without any
cleansing) and you need to list out all customer names and phone numbers
(with no alphabet). The OpenSQL statement does not support regular
expression, you need to loop through the internal table and use REPLACE
ALL OCCURRENCE to replace the string.

ABAP Code:
SELECT Name, Phone FROM ZCUST_TABLE INTO TABLE @DATA(lt_cust).
LOOP AT lt_cust ASSIGNING @<FIELD-SYMBOL>(<fs_cust>).
REPLACE ALL OCCURRENCES OF REGEX '([^\d])' IN <fs_cust>-phone WITH ''.
ENDLOOP.

AMDP Code: You can implement the same In AMDP Script as follow:

et_cust = SELECT Name, REPLACE_REGEXPR(‘([^\d])’ IN phone WITH ‘’) AS phone

FROM ZCUST_TABLE;

The following code snippet is to list out the vendors with the name ‘SCOTT’
or ‘AWAY’ with case-insensitive.

et_lfa1 = SELECT * FROM LFA1 WHERE text LIKE_REGEXPR ' scott|away ' FLAG i;
Built-in Libraries

SAP SQLScript provides a set of built-in libraries to provide more

functionalities. You can create your libraries through the HANA database
tool. How to create a user-defined library is out of the scope of the book.
You can declare the usage of a library with the USING statement.
Library Description
SQLSCRIPT_STRING Offers multiple libraries to manipulate strings such as
splitting the string. It has both SPLIT and FORMAT
family functions.
SQLSCRIPT_SYNC Offers procedures SLEEP_SECONDS and
WAKEUP_CONNECTION. The library has limitations
such as it cannot be used in functions and calculation
views.
SQLSCRIPT_PRINT The library prints string or tables. This does not apply to
the AMDP procedure.
SQLSCRIPT_LOGGING Supports user-level tracing for various types of SQLScript
objects.

Scenario: You need to split the string into table. The following code snippet
demonstrates to split the string into the table using LIB: SPLIT_TO_TABLE.

METHOD sqlscrit_string_split BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.
USING SQLSCRIPT_STRING AS LIB;
-- note that when you split to table always return to the result column
et_out = LIB:SPLIT_TO_TABLE( :im_string, ',' );
ENDMETHOD.

Result:
Instead of splitting to a table, you can split to the variables like ABAP
programming.

METHOD sqlscript_string_split BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.
USING SQLSCRIPT_STRING AS LIB;
DECLARE col1, col2, col3 VARCHAR(30);
-- note that when you split the variables
(e_col1, e_col2, e_col3) = LIB:SPLIT( :im_string, ',' );
ENDMETHOD.

The format function of SQLSCRIPT_STRING library is similar to the Python

string format function. The format function enables you to format the
specified values and insert them inside the string’s placeholders. The
placeholder is defined using ‘{}’.

Scenario: Create a comma-delimited file from the database table. The

AMDP procedure can generate an array of records with comma-delimited
data. You can use the ABAP program to download the internal table into a
file.

AMDP Code:
You can use the SAP HANA SQLScript library SQLSCRIPT:
FORMAT_TO_TABLE. The function formats each record in the table
variable and creates a table of strings.
METHOD sqlscript_string_format BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
USING lfa1 geoloc.

USING SQLSCRIPT_STRING AS LIB;

lt_vendor = SELECT lifnr, adrnr, ort01 as city, longitude,latitude

FROM lfa1 l
INNER JOIN geoloc as g on l.adrnr = g.addrnumber
WHERE mandt = SESSION_CONTEXT( 'CLIENT' );

et_out = LIB:FORMAT_TO_TABLE(
'{lifnr},{addrnumber},{city},{longitude},{latitude}', :lt_vendor );

ENDMETHOD.
Scenario: You need to get message text like ABAP’s MESSAGE-INTO
clause. For a given message-id, message number, and input string
(parameters into comma delimited string), you need to get message text.
See the following code snippet.

METHOD get_message_text BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.
USING SQLSCRIPT_STRING AS LIB;

DECLARE lv_message VARCHAR( 100 );

DECLARE lt_arg VARCHAR( 30 ) ARRAY;
DECLARE lv_occur INTEGER;

lt_arg = LIB:SPLIT_TO_ARRAY( :iv_args, ',' ); -- parameters

-- replace & or &<n> INTO {}
SELECT REPLACE_REGEXPR( '\&+' IN TEXT with '{}' OCCURRENCE ALL )
into LV_MESSAGE FROM T100
WHERE ARBGB = :iv_msg
AND msgnr = :iv_msgno
AND SPRSL = SESSION_CONTEXT( 'LOCALE_SAP' ) ;

-- Fill any missing parameters with an empty string.

--SQLSCRITPT_STRING:FORMAT requires the array bounded for {}. You need to
fill empty
SELECT OCCURRENCES_REGEXPR( '\{}' IN :lv_message ) INTO lv_occur
FROM DUMMY;
IF cardinality( :lt_arg ) < lv_occur THEN
DECLARE lv_diff INTEGER = lv_occur - cardinality( :lt_arg );
DECLARE lv_cnt INTEGER;
DECLARE lv_card INTEGER := CARDINALITY( :lt_arg );
for lv_cnt in 1..lv_diff do
lt_arg[ lv_card + lv_cnt ] = '';
END FOR;
END IF ;
ev_out = LIB:FORMAT( :lv_message, :lt_arg ); -- Return message text
ENDMETHOD.

Hamming distance is how many different symbols are between two equal-
length strings. The basic purpose is to measure the minimum number of
substitutions required to change one string into the other. SAPScript
provides the function HAMMING_DISTANCE to perform a bitwise or
bytewise comparison between two arguments and returns the hamming
distance. The function returns -1 when the length of strings is different.
The HAMMING_DISTANCE supports the BINARY data type also
(compare for each bit).
 DECLARE lv_hdist INTEGER;
-- first one returns 0; second returns 1 and third returns 3
SELECT HAMMING_DISTANCE('abc', 'abc') FROM DUMMY INTO lv_hdist;
SELECT HAMMING_DISTANCE('abc', 'abd') FROM DUMMY INTO lv_hdist;
SELECT HAMMING_DISTANCE('abc', 'cab') FROM DUMMY INTO lv_hdist;

Unicode Normalization

The Unicode encodes the text elements as precomposed forms (canonical

decomposition) and combines character sequences. Unicode normalization
means consistently picking one of these equivalent encodings, that is, either
all composed or all decomposed. SAP supports all four normalization types
viz., NFD (Normalization Form D – Canonical Decomposition), NFC
(Normalization Form – Canonical Decomposition and Canonical
Composition), NFKD (Normalization Form KD – Compatibility
Decomposition), and NFKC (Normalization Form KC – Compatibility
Decomposition and Canonical Composition). You can use the ABAP
method CL_ICU_NORMALIZATION=> NORMALIZE to normalize the
input string. You can mention the normalization form as the parameter
(IM_NORMMODE: 2-NFD, 3-NFKD, 4-NFC, and 5-NFKC). SQLScript the
function NORMALIZE to normalize the string.

DECLARE lv_norm_str NVARCHAR(100);

SELECT NORMALIZE('A °', ‘NFC’) INTO lv_norm_str FROM DUMMY; -- NFD,
NFKC, NFKD other options

Array Processing

In SQLScript, an array is an indexed collection of elements with a single

data type. The table is an array of structured data. The main advantage of
the table-type variable is that it can be used as the data source in the
SELECT statement. You cannot use an array as your data source in the
SELECT statement. Major functionalities can be implemented in the
SELECT statement using its power SQL functions. The requirement of
using loop the table type is very minimal. You can declare an array variable
with the elementary type or table type. The array index can have any value
between 1 and 2147483646 (231 – 1). SQLScript index-based access is error-
tolerant. When you try to read a row that doesn’t exist in the table variable,
the system returns the NULL value instead of the error.
 DECLARE lv_arr INTEGER ARRAY; -- array definition
DECLARE lt_tbexample TABLE (col1 INTEGER, col2 VARCHAR(50) ); -- table type

In ABAP, you can access the table-typed column value using lt_tab[1]-col1.
It raises the runtime error ITAB_LINE_NOT_FOUND when the array index
is out of bounds. The common practice is to use a field symbol to update
the column values in the table.

LOOP AT lt_tab ASSIGNING FIELD-SYMBOL(<fs_tab>).

IF <fs_tab>-col1 > 10.
<fs_tab>-col2 = <fs_tab>-col12 + <fs_col>-col3.
ELSE.
<fs_tab>-col2 = <fs_tab>-col12 - <fs_col>-col3.
ENDIF.
ENDLOOP.

SQLScript provides multiple array functions to connect between the table

type and scalar array. You can process the table type and array using the
LOOP concept. See the following code snippet, it processes the table type
variable from the AMDP method and updates col2 based on the col1 value.
The basic idea of the snippet is to demonstrate the LOOP control and
accessing and setting the values of the table-type variable. Based on the
complexity of the problem, use the LOOP control at your discretion.

DECLARE cnt integer;

-- FOR cnt in 1..(SELECT COUNT(*) FROM :it_tab) DO -- it is possible
FOR cnt in 1..RECORD_COUNT(:lt_tab) DO
IF lt_tab.col1[cnt] > 10 THEN
lt_tab.col2[cnt] = lt_tab.col12[cnt] + lt_tab.col3[cnt];
ELSE
lt_tab.col2[cnt] = lt_tab.col2[cnt] – lt_tab.col3[cnt];
END IF;
END FOR;

For table-type variables, you can directly assign the value through the index
access. See the following code snippet to update the table type variable.

lt_tab[3] = (‘col1’, ‘col2’, ‘col3’);

--or
:lt_tab.UPDATE((‘col1’,’col2’,’col3’), 3);
The RECORD_COUNT function returns the number of records in the table
type. You can use the function if the number of records is not used in the
FOR code block. Otherwise, you can declare the count variable and
populate the value from the RECORD_COUNT or the SELECT COUNT(*)
statement. SAPScript provides functions that convert between array and
table columns.
• ARRAY_AGG – Copy the column data into an array with the same data
type.
• UNNEST – Fill in the table column values from the array. Additionally, the
function supports filling the ordinal value along with the specified column.
You can use the WITH ORDINALITY clause, you can specify the ordinal
column name.
• TRIM_ARRAY – Removes elements from the end of an array
• SUBARRAY – Returns the part of the array of a specified length on the start
position
• CARDINALITY - Returns the highest index of a set element in the array
• CONCAT – Concatenates two array variables

lv_arr = ARRAY_AGG(:lt_tbexample.col1 ORDER BY col2 DESC NULLS FIRST) ;

lv_arr = ARRAY_AGG(:lt_tbexample.col1 ORDER BY col2 DESC NULLS FIRST) ;
-- without the ordinal column
lt_tbexample = UNNEST(:lv_arr) AS (col1);
-- with the ordinal column
lt_tbexample = UNNEST(:lv_arr) WITH ORDINALITY AS (col1, col2); - col2 will
have the sequence
--remove the last entries from the array
lv_arr1 = TRIM_ARRAY(:lv_arr,2); -- deletes last two elements from array
lv_arr2 = SUBARRAY(:lv_arr, 2,3) – returns the 3 elements from the start position
-- concatenate two arrays
lv_arr3 = CONCAT(:lv_arr1, :lv_arr2);

Scenario: Consider that your input table has a column with integer values.
You need to generate a table with two columns. The first column should
have column values in the input table in ascending order and the second
column should have descending order values.

AMDP Code:
METHOD sqlscript_string_format BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT.
t1 = SELECT col1 from :it_tab ORDER by col1 ASC;
t2 = SELECT col1 from :it_tab ORDER by col1 DESC;
et_tab = UNNEST( :t1, :t2 ) AS ( col1, (col1 as col2) );
ENDMETHOD.
Due to the restriction of ABAP, the AMDP method does not support the
array as the parameter. AMDP methods support only table-type

Session Management

You can implement the session variables in multiple ways in ABAP. The
common ways to handle this:
• Static ABAP class and static attributes and static methods to set/read the
variables.
• Function group and methods to set/read the variables
• Import and Export to Memory statements

SQL Script uses the SYS.M_SESSION_CONTEXT table under the session

variable. You can set and unset your variables in the session to be used in
the different programs. In ABAP, the data cluster is used to import the
variables into memory using the statements IMPORT TO MEMORY and
EXPORT FROM MEMORY. Or you can use a static ABAP class to retain the
custom session variables. The statement FREE MEMORY is used to free
the variables from memory. Similarly, the UNSET statement unsets the
variable from the context.

AMDP Code:
METHOD method1 BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT.
SET ‘CONTXT_VAR1’ = IM_VAL;
ENDMETHOD.

METHOD method2 BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.
ex_out = SELECT SESSION_CONTEXT(‘CONTXT_VAR1’) FROM DUMMY;
ENDMETHOD2.

METHOD method3 BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT.
UNSET ‘CONTXT_VAR1’;
ENDMETHOD.

The SESSION_CONTEXT has many predefined variables. You can see a

few of the predefined variables in the following table.

Variable Description
 DATE_FORMAT Date format
TIME_FORMAT Time format
TIMESTAMP_FORMAT Timestamp format
CASE_SENSITIVE Flag for whether case sensitive
CLIENT Client number
LOCALE_SAP Login Language

Database Transaction Management

The AMDP method supports INSERT, UPDATE, REPLACE and DELETE

statements. You should not use the OPTIONS READONLY statement in
the procedure. The REPLACE statement updates rows in a table or inserts
new records. The UPSERT and REPLACE have no difference and these are
synonymous. Currently, the AMDP methods are not supporting the
creation of a global or local temporary table.

HANA SQL Database transaction management enables you to handle

multiple transactions in one logical unit of work using the SAVEPOINT
concept. You can set the savepoint as a location in which a transaction can
return if part of the transaction is conditionally canceled. You can proceed
to complete more transaction SQL statements. Once you committed then
the SAVEPOINT vanishes. You can use the statement RELEASE
SAVEPOINT to release it. In ABAP, the logical unit of work works as a
single transaction either you can save the data using commit work or cancel
the data using rollback work.

The AMDP method has a lot of restrictions in SQLScript database

transaction management. AMDP methods are not supporting any of the
database transaction statements (such as COMMIT, ROLLBACK) to ensure
data consistency. The AMDP method is implemented in the HANA
database; not on the ABAP and the ABAP runtime has no information
about any transactional statements such as COMMIT or ROLLBACK in the
AMDP method.
Error Handling
NULL is a special marker used in SQL to indicate that a data value does not
exist. Do not confuse the NULL value with ABAP’s INITIAL value. In
ABAP, when you declare the variable, by default it is assigned an initial
value based on the data type (0 for numeric, and ‘’ for character type).
When you declare a variable in SQLScript, a null value is assigned by
default. Any arithmetic operations on a NULL value give an unknown
result. When you do concatenate a NULL value with other strings, the
result is NULL only. Also, you cannot handle comparison operators ( =, <,
>, or <> ) on a NULL value. You can use the ‘IS NULL’ or the ‘IS NOT
NULL’ to test whether the data value is NULL or not.

SQLScript provides two COALESCE AND IFNULL statements to handle

the null values. The main difference between the statements is that the
IFNULL function takes two arguments and returns the second argument
when the first argument is NULL. The COALESCE allows you to return the
second argument if it is a NULL and the third argument when the third
argument is NULL. Technically, it returns the first non-NULL values from
the arguments.

Like NULL, the divide by zero error is one of the common errors. In ABAP,
you can catch the exception CX_SY_ZERODIVDE when the divisor is zero.
SQL error 304 is raised when the SQL raises the exception when the data
value is divided by zero. You can catch the error or you can avoid it using
the SQL function NDIV0. The NDIV0 function returns zero when the value
is divided by zero, otherwise, it returns the actual value.

Exception Handling

In ABAP, the exceptions are events that arise during the execution of the
program when it is not able to continue in a meaningful way. The
exception can cause the termination of the program. The exception
handling process enables you to handle the exceptions and avoid any
unnecessary short dumps.
ABAP supports two types of exceptions: classic exceptions and class-based
exceptions. The classic exceptions can be only declared in the interfaces of
methods or function modules using EXCEPTIONS. It can be raised using
the RAISE or MESSAGE RAISE statements. You can handle it using the
return codes of the system field SY-SUBRC. ABAP’s class-based exceptions
are defined by the exception classes. The exception class is created when
you raise a class-based exception and it can be further propagated by any
procedures.

The main purpose of class-based exceptions is to separate the exception-

handling logic from the main programming logic. The exceptions can be
handled in TRY blocks (TRY, ENDTRY, CATCH, and CLEANUP). The
CATCH block handles the exceptions. Before the CATCH block is
executed, by default, ABAP deletes the context in which the exception was
raised. To avoid it, you can use the CATCH BEFORE UNWIND statement.
The CLEANUP block offers you the opportunity to clean up the inner TRY
block.

The exception handling provides two special statements RETRY and

RESUME. Once you processed the exception block and if you want to retry
the TRY block again, you can use the RETRY statement. The caveat is you
need to take care of the exception does not re-occur. Otherwise, it causes an
endless loop of exceptions. The RESUME statement enables resuming
processing after the statement that raised the exception. The pre-requisite
to using a RESUME statement is that you need to raise the exception using
the keyword RAISING RESUMABLE and the context of the exception
should be available. To keep the context of the exception, use CATCH
BEFORE UNWIND.

SAP provides several AMDP exception classes with the prefix CX_AMDP.
Note that you cannot define any exception in the AMDP function. The base
AMDP exception class is CX_AMDP_ERROR (subclass of
CX_DYNAMIC_CHECK). The list of AMDP exception classes is in the
following table.

Exception Class Description

CX_AMDP_ERROR Base AMDP class exception
CX_AMDP_VERSION_ERROR Raises when the AMDP version management
error
CX_AMDP_CREATION_ERROR Raises when the creation of the database
procedure fails
CX_AMDP_EXECUTION_ERROR Raises when the execution of the DB
 procedure fails
CX_AMDP_CONNECTION_ERROR Raises when the database connection fails
CX_AMDP_SQL_EXCEPTION Raise when SQL statement fails
CX_AMDP_RESULT_TABLE_ERROR Error in import parameter handling during
the execution of an AMDP method

SQL Exception Handling

SQL exception handling gives you control over the SQLScript procedure.
When the exception occurs, the following SQL operations are suspended.
SQLScript provides multiple exception operators viz., EXIT HANDLER and
CONTINUE HANDLER for exception handling. The EXIT handler in
SQLScript offers a way to process exception conditions in a procedure or a
function during execution. The EXIT handler is equivalent to ABAP’s
CATCH statement. The CONTINUE handler not only allows you to handle
the error but also to continue with the execution after an exception has been
thrown. The CONTINUE is equivalent to the RESUMABLE exception.
The DECLARE EXIT HANDLER parameter allows you to define an exit
handler to process exception conditions in your procedure or function.

DECLARE EXIT HANDLER FOR SQLEXCEPTION

lt_error = SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM
DUMMY;
INSERT INTO MYTAB VALUES (1);
INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301

The Conditional SQL error exception is handling a particular SQL error

code.

-- Assume the lt_tab has no record

DECLARE myVar INT;
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299
BEGIN
et_err = SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
FROM DUMMY;
et_tab = SELECT ‘’ AS col1, ‘’ as COL2, ‘’ as COL3 from dummy;
END;
SELECT I INTO myVar FROM :lt_tab; --NO_DATA_FOUND exception
-- the following block will be never reached
You can define your exception under the BEGIN...END block so that you
can handle exporting parameters that are required to send back to calling
ABAP program.

Note that if you have any exporting parameters (like the table type or
scalar) to pass back to the ABAP code then make sure to handle the code
while handling the exception. You can declare a condition on a user-
defined error code or an SAP HANA error code. It helps you to get better
readability of the code.

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;

DECLARE EXIT HANDLER FOR MYCOND
lt_err = SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
FROM DUMMY;

You can get a complete list of error codes using the following query.

SELECT * FROM M_ERROR_CODES ORDER BY CODE ASC;

The SQLScript provides the DECLARE CONTINUE HANDLER option that

offers you not only to handle the error but also to continue with the
execution.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

lt_error = SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE
FROM DUMMY;
INSERT INTO MYTAB VALUES (1);
INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301
… -- CONTINUE with the code of logic

Note that the implicit or explicit parallel execution of the SQL statements is
not supported in the EXIT or CONTINUE handler. EXIT handlers cannot be
declared within the same scope or a nested scope of a CONTINUE handler,
but CONTINUE handlers can be declared within the nested scope of an
EXIT handler.

The list of commonly used SQL Error Codes is listed in the following table.

Code Message
257 SQL syntax error
301 Unique constraint violated
 304 Division by zero undefined
305 The single-row query returns more than one
row
307 Numeric value out of range
314 Numeric Overflow
315 Invalid synonym
407 Invalid date format
426 Number of columns mismatch
1299 No data found
1318 Built-in function not defined
5088 Hierarchy error

User-defined Errors

You can declare a user-defined error code, where the error code is between
10000 and 19999. SIGNAL or RESIGNAL can be used to throw a user-
defined exception in the SQL procedure. You can signal or re-signal a user-
defined error code for your business conditions.

DECLARE invalid_data CONDITION FOR SQL_ERROR_CODE 10001;

DECLARE EXIT HANDLER FOR MYCOND
BEGIN
lt_err = SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM
DUMMY;
END;
lt_po = SELECT EBELN, …. FROM EKKO WHERE <business logic>;
IF ::ROWCOUNT = 0 THEN
SIGNAL invalid_data SET MESSAGE_TEXT = 'No valid data found for
selection';
ENDIF;

The difference between SIGNAL and RESIGNAL is that when using

SIGNAL, you must point out the SQL ERROR CODE or CONDITION of the
exception. But RESIGNAL can be used in the action part of an EXIT
HANDLER all alone. That is to say, RESIGNAL can be used to throw an
exception of the EXIT HANDLER catches to the caller. You can retrigger the
last exception using the RESIGNAL clause. A user-defined exception can
be caught by the corresponding EXIT HANDLER, or caught by the caller,
the same as an exception containing a standard error code.
Full-Text Search

Searching is always a challenging task because searching particular strings

in the database are often difficult for any kind of search. For instance,
writing names can be done in different ways such as non-standardized
abbreviations and mixed cases and it will not render precise searching with
equal or like operators. SAP full-text search capabilities support 32
languages, linguistic, and fuzzy search. The full-text search supports text
analysis (tokenization, stemming, complex semantic analysis) and text
mining (semantic determination). Note that full-text search is only
applicable to the HANA database. Full-text search is a big topic and the
book covers a few AMDP methods to handle some features of full-text
search.

The fuzzy search function handles full-text search capabilities with a degree
of error tolerance. The error tolerance is used to find information even if
there are slight misspellings or related words. SAP offers an error tolerance
between 0 and 1 (where 1 is a perfect match). SAP defaults it with 0.8.
Fuzzy search calculates the fuzzy score (between the search term and the
data value) to understand the error tolerance. When full-text search on the
text column, the fuzzy score uses the term frequency and inverse document
frequency (TF/IDF) score instead of the actual fuzzy score.

To better performance on fuzzy search capabilities, the full-text index is

required for the relevant table and columns. From ABAP release 7.4 SP03,
SAP enables you to create the full-text index using the ABAP dictionary as a
regular index. You can access the full-text search on the index object menu
item Goto -> Full text Index. When you create a full-text index, SAP
attaches a hidden column to the table so that SAP HANA can perform the
search. Note that a full-text index increases the footprint of the table in
memory and when you activate a full-text index with synchronous update
mode for a big table may take a long time to create. See the following
screen for the Full-text index.
ABAP release 7.4 SP05 enhances the search help dictionary object with
proposal search. The proposal search automatically proposes the matching
values when you are entering a few characters. You can enhance the
proposal search if you combine it with a fuzzy full-text search.

SAP HANA provides a powerful CONTAINS predicate that enables you to

do a full-text search. The usage of CONTAINS has a similar look and feel to
common search engines. You can search more than columns (within the
same table) with the CONTAIN predicate. The WHERE clause can have
multiple CONTAIN predicates. The predicate allows three types of search
functions viz., EXACT, LINGUISTIC, and FUZZY. The EXACT search
returns the record only when the search term is matched exactly as
specified. The LINGUISTIC search allows you to find all words that have
the same word stem as the search term. You can create your term mapping
and full-text indexed.

The scalar functions such as SCORE, HIGHLIGHTED, and SNIPPETS are

available to get additional information about CONTAIN predicate results.
The SCORE function returns the relevance of a record that has been found.
The SNIPPETS function provides what word is considered in the full-text
search.
AMDP Code: The following code snippets provide the plnnr object
number, score, and snipped text (highlighted with <b>) for the given search
element. The fuzzy tolerance is set to 0.7.
METHOD fulltextsearch
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING PLKO.

et_data = SELECT PLNNR, SCORE( ) AS SCORE,

SNIPPETS( KTEXT ) as snippet
FROM plko
WHERE CONTAINS ( ktext, :iv_search, FUZZY( 0.7 ) );
ENDMETHOD.

Selection Criteria

One of the important issues raises when you code pushdown is how to
handle select options in the AMDP procedure i.e., how can we pass the
select-options to the AMDP method? Consider that the program has
multiple select-options, and you need to pass select options to the AMDP
method to filter the select statements in SQLScript.

You can use the ABAP method CL_SHDB_SELTAB=>COMBINE_SELTABS

to convert all select options into a where clause. You can apply dynamic
filters using the function APPLY_FILTER. Note that all columns in the
where conditions must be the table variable. If any columns are missing,
the AMDP will be raised the runtime error. You can see it in the following
ABAP code snippet. The parameters can be defined directly as the import
parameters. Note that there is a parameter iv_client_field. The parameter
adds ‘CLIENT = <current client>’ in the where clause. The assumption is
that your filtering table will have the column CLIENT with the current
client number.

DATA(lv_where1) = cl_shdb_seltab=>combine_seltabs(
it_named_seltabs = VALUE #(
( name = 'EKORG' dref = REF #( s_ekorg[] ) )
( name = 'EBELN' dref = REF #( s_ebeln[] ) )
( name = 'LIFNR' dref = REF #( s_lifnr[] ) ) )
iv_client_field = 'CLIENT' ) .
The sample AMDP method code to handle the APPLY_FILTER to handle
your selection criteria.

METHOD get_po_details BY DATABASE PROCEDURE FOR HDB LANGUAGE

SQLSCRIPT OPTIONS READ-ONLY USING ekko lfa1.
LT_PO =
SELECT ekko.mandt as client, ekko.ebeln, ekko.bukrs, ekko.lifnr
FROM ekko
INNER JOIN lfa1 ON lfa1.lifnr = ekko.lifnr.
LT_PO = APPLY_FILTER( :LT_PO, :IM_WHERE );
ENDMETHOD.

AMDP Enhancement

Business Add-In (BAdI) is an object-oriented enhancement option, a source

code plug-in (hook) to enhance the well-defined business function without
making changes to the delivered source code. To enhance the AMDP
method, you can use AMDP BADI. As a software provider, you can define
a BAdI definition marked as AMDP BADI. You must define a fallback
implementation for the BADI. As a customer, you can enhance the AMDP
by creating a BAdI implementation with SQLScript coding. If there is no
BAdI implemented then the fallback implementation is being executed.
You can invoke the AMDP implementations with the regular BAdI methods
GET BADI and CALL BADI. You can implement multiple implementations
(for multiple-use options). If you want to merge the results with co-existing
implementations, you can use UNION or UNION ALL to merge the results.
 You can see the BADI method as the data source

The BADI is invoked.

You can make the fallback implementation as simple as follows. Just

returns the same result table.

Create the implementation using the transaction code SE19. In the

implementation(s), you can process the result table based on your business
logic.
.
Aggregation
SQL aggregate expression enables you to aggregate the value of multiple
rows via an aggregate function. The purpose of the chapter is to explain the
aggregate, subquery, set operations, grouping set, and windows functions.
The aggregate function uses the GROUP BY clause to aggregate. SQLScript
provides a GROUPING SET operator to handle multiple grouping into one
SQL statement. The chapter discussed the grouping sets along with options
for grouping sets. SQLScript window function enables you to aggregate
the multiple rows within the resultant set without the GROUP BY clause.
The chapter discusses most of the Windows functions with possible
scenarios.

Aggregate Functions

SQL aggregation is the process of collecting a set of values and returning a

single value using the help of aggregate functions such as sum, count, min,
max, and avg. The aggregation functions are always deterministic i.e.,
aggregate functions return the same value each time. The common SQL
aggregation is based on the GROUP BY clause. ABAP OpenSQL aggregates
use GROUP BY and HAVING clauses to handle the aggregations.
OpenSQL supports aggregate expressions including AVG, MAX, MIN,
SUM, and COUNT. SAP HANA provides a wide range of aggregation
functions including sum, count, min, max, avg, variance, standard
deviation, correlation, first_value, last_value, Nth Value, etc. Also,
SQLScript supports aggregate functions in the window function.
One of the common aggregation scenarios is pivoting the data. Pivoting
data refers to converting rows information to column information.
SQLScript does not provide any specific operator for PIVOT. You can use
the CASE WHEN clause to pivot the rows into columns. The following
code snippet is to demonstrate the pivot function that lists the sales at year,
every month (column-wise) for sales org. The purpose of the code is to
demonstrate GROUP BY and pivoting techniques. You can do the same in
ABAP coding.

et_orgsales = SELECT h.vkorg, SUM(i.netpr) as AnnualSales,

SUM(CASE WHEN QUARTER(h.erdat) = 1 THEN i.netpr ELSE 0 ) as JanSales,
SUM(CASE WHEN QUARTER(h.erdat) = 2 THEN i.netpr ELSE 0 ) as FebSales,
SUM(CASE WHEN QUARTER(h.erdat) = 3 THEN i.netpr ELSE 0 ) as MarSales,
-- ……
 -- ……
SUM(CASE WHEN QUARTER(h.erdat) = 11 THEN i.netpr ELSE 0 ) as NovSales,
SUM(CASE WHEN QUARTER(h.erdat) = 12 THEN i.netpr ELSE 0 ) as DecSales
FROM vbak h
INNER JOIN vbap i ON h.vbeln = i.vbeln
WHERE mandt = SESSION_CONTEXT( ‘client’ ) – AND your conditions
GROUP BY h.vkorg;

Scenario: Get records of the sales record which is the maximum for each
product group for the record. You cannot able to use the GROUP BY
statement.

ABAP Code:
Approach 1:
lt_prod[] = lt_product_sales[].
Delete adjacent duplicates from lt_prod COMPARING BY product.
SORT lt_product_sales BY product, sales DESC.
LOOP AT lt_prod INTO dat(lv_prod).
READ lt_product_sales INTO DATA(lv_product) WITH KEY product =
lv_prod.product.
LOOP AT lt_product_sales INTO data(lv_product1).
WHERE product = lv_prod.product and sales = lv_product.sales.
APPEND lv_product1 TO lt_max_sales.
ENDLOOP.
ENDLOOP.

Approach 2 with CTE: Note that the ABAP common table expression (CTE)
supports ABAP 7.51.

WITH
+prod AS (
SELECT product, max(sales) as sales FROM ZT_SALES
GROUP BY product
)
SELECT s1.* “ List your fields
FROM ZT_SALES AS s1
INNER JOIN +prod AS p
ON s1~product = p~product AND s1~sales = p~sales
INTO TABLE @DATA( lt_result ).

AMDP Code:

lt_max_sales = SELECT s1.*

FROM :lt_product_sales s1,
(SELECT product,max(sales) as sale
 from :lt_product_sales
group by product) max_sales
WHERE s1.product=max_sales.product
AND s1.sale=max_sales.sale;

HANA SQL implements aggregation functions that are not just based on
the group by statement. It uses the PARTITION BY clause that divides the
result set produced by the FROM clause into partitions to which the
function is applied.

Subquery

A subquery is a nested SQL query within a SQL query. The basic purpose of
the subquery is to combine two steps. SQLScript supports the subquery in
the SELECT clause, the FROM clause, and the WHERE (including the JOIN
statement) clause. It supports up to 50 subqueries in a single query. The
ABAP OpenSQL supports only the WHERE clause subquery. The
subqueries can be used in SELECT, INSERT, UPDATE, and DELETE
statements. The subquery is always enclosed in parentheses. The
SQLScript supports the subquery types:

• A single row/column subquery – Returns exactly one column and it is

usually used in the SELECT clause. When you are using a subquery in the
SELECT clause, make sure that the subquery returns a single value.
Otherwise, the SQL exception will be raised.
• Multiple rows subquery – Returns one or more records and it is used in the
FROM and the WHERE clause subquery.
• Correlated Subquery - The subquery that refers to the columns from the
outer query. It is row-by-row processing and the subquery is executed for
every row of the outer query.
• Nested Subqueries – Subquery is placed within another subquery

The subquery is very flexible so that you can implement complex business
scenarios. You can divide the complex query into multiple queries with
logical steps. Use the subquery when the result that you want requires
more than one query and each subquery provides a subset of the table
involved in the query.
The following code snippet demonstrates the usage of the subquery. The
code fetches the sales order, sales organization, and how many sales orders
at the sales organization. You can provide multiple ways to implement it.

-- Subquery in the select clause

et_so =
SELECT vbeln, vkorg, auart,
SELECT ( count(*) from VBAK as v2 where v1.vkorg = v2.vkorg) as cnt
FROM VBAK as v1;
-- or subquery in the INNER JOIN (from clause)
et_so =
SELECT v1.vbeln, v1.vkorg, v1.auart, vkorg.cnt as cnt
FROM VBAK as v1
INNER JOIN ( select vkorg, count( * ) as cnt
from vbak as v2
where v2.vkorg = v1.vkorg) as vkorg
-- or windows function. You can see it in the section Windows Function in the next
chapter
et_so =
SELECT vbeln, vkorg, auart,
cnt(vbeln) OVER ( PARTITION by vkorg ) as cnt
FROM VBAK as v1;

In the WHERE clause subquery, you can use the subquery as the predicate
or the expression. SAP SQLScript supports the EXISTS and IN clauses. The
EXISTS operator is a logical operation that is used to validate the existence
of any record from the subquery and it returns TRUE if the subquery
returns at least one row otherwise, it returns FALSE. You can use the
subquery in the IN clause. You can validate that the column value is within
the values returned by the subquery. You can use multiple columns in the
IN clause. If the subquery returns a huge number of records, then do not
use the IN statement.

The following code snippet lists all the sales order items that match with a
given set of sales orders and items (as a part of input parameters it_sales).

--subquery with multiple columns with IN operator

et_sales = SELECT vbeln, posnr, matnr, netpr
FROM VBAP
WHERE (matnr, netpr ) IN
( SELECT matnr, netpr FROM :it_sales );

-- OR EXISTS (correlated subquery) instead of IN

et_sales = SELECT vbeln, posnr, matnr, netpr
FROM VBAP as v1
 WHERE EXISTS
( SELECT matnr, netpr FROM :it_sales as s1
WHERE s1.matnr = v1.matnr
AND s1.netpr = v1.netpr
);

Universal Quantifiers

In predicate calculus, the universal quantifier is a type of quantification that

can be interpreted as ‘given any’ or ‘for all’. To support universal
quantification, SAP SQLScript provides comparison predicates ANY,
SOME, and ALL. Instead of using EXISTS subquery, you can use ANY,
SOME, or ALL predicates to get the same result. The predicates ANY and
SOME do the same thing and they are completely interchangeable.
The following code snippet is to get all sales orders where the trading
partner (KUNNR) is in Maryland state. You can implement the same with
INNER JOIN and EXISTS statements also. The purpose is to demonstrate
the usage of ANY statement.

METHOD univ_any_so
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING VBAK
KNA1.
et_so =
SELECT vbeln, vkorg, auart, bstnk, kunnr
FROM VBAK
WHERE KUNNR = ANY ( SELECT KUNNR FROM KNA1 WHERE REGIO = 'MD'
);
ENDMETHOD.

Consider the requirement that you need to list out all sales orders of plant
PLT1 where its net price is higher than any of the sales orders of the same
material in plant PLT2.

METHOD univ_all_so
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING VBAK
KNA1.
et_so1 = SELECT vbeln, posnr, matnr, netpr
FROM VBAP AS V1
WHERE WERKS = 'PLT1'
and NETPR > ALL ( SELECT NETPR FROM VBAP as v2
WHERE WERKS = 'PLT2' and v1.matnr = v2.matnr );

ENDMETHOD.
The same can be implemented with ANY statement (using the negation
rule).
METHOD univ_all_so
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING VBAK
KNA1.

et_so1 = SELECT vbeln, posnr, matnr, netpr

FROM VBAP AS V1
WHERE WERKS = 'PLT1'
and NETPR <= ANY ( SELECT NETPR FROM VBAP as v2
WHERE WERKS = 'PLT2' and v1.matnr = v2.matnr );

ENDMETHOD.

Set Operations
Set operators are used to combine or exclude the results of two or more SQL
queries. The SQL statements in the set operations must have matching
columns in the same order and of a compatible type. SAPScript supports
the following SET operators.
• UNION – Combines the result of two or more select statements and returns
the distinct result
• UNION ALL – Combines the result of two or more select statements and
returns all the result set (possible duplicate records)
• INTERSECT – Combines the result of two or more select statements and
returns all common rows
• EXCEPT – This is equal to MINUS. Takes the result from the first query
and then removes the rows selected by the selection query

See the following code snippet to demonstrate the SET operators.

-- List all countries

et_so1 = SELECT DISTINCT land1 as country
FROM LFA1
UNION
SELECT DISTINCT land1 as country
FROM KNA1;
-- List all countries that serve both customers and suppliers. Exclude country has no
customer
-- or supplier
et_so2 = SELECT DISTINCT land1 as country
FROM LFA1
INTERSECT
 SELECT DISTINCT land1 as country
FROM KNA1;
-- you can implement the INTERSECT with subquery
et_so2 = SELECT DISTINCT land1 as country
FROM LFA1
WHERE land1 IN ( SELECT distinct land1 FROM KNA1 );
-- List all countries serves only suppliers only (no customers)
et_so3 = SELECT DISTINCT land1 as country
FROM LFA1
EXCEPT
SELECT DISTINCT land1 as country
FROM KNA1;
-- List the countries has only customer or supplier (not both)
et_so4 = SELECT * FROM et_so1
EXCEPT
SELECT * from et_SO2;

Grouping Set
You can group by the number of fields in the ABAP SELECT statement to
aggregate the results using the grouping fields. You can use the grouping
set to return multiple sets of aggregation in a single result set. The
grouping set is equivalent to the UNION ALL of all aggregate results of
each group.

Scenario: Get the summary of accounting documents grouped by Company

code and G/L Account, Company Code and Fiscal Year, Company Code
and Business Area, and Company code and profit center.
ABAP Code:

select bukrs, gjahr, ‘’ as hkont, ‘’ as gsber, ‘’ as prctr,

sum(dmbtr) as dmbtr, sum(wrbtr) as wrbtr,
sum(dmbe2) as dmbe2, sum(dmbe3) as dmbe3, count( * ) as count
from bsis
GROUP BY bukrs, gjahr
UNION
select bukrs, ‘’ as gjahr, hkont, ‘’ as gsber, ‘’ as prctr,
sum(dmbtr) as dmbtr, sum(wrbtr) as wrbtr,
sum(dmbe2) as dmbe2, sum(dmbe3) as dmbe3, count( * ) as count
from bsis
GROUP BY bukrs, hkont
UNION
select bukrs, ‘’ as gjahr, ‘’ as hkont, gsber, ‘’ as prctr,
 sum(dmbtr) as dmbtr, sum(wrbtr) as wrbtr,
sum(dmbe2) as dmbe2, sum(dmbe3) as dmbe3, count( * ) as count
from bsis
GROUP BY bukrs, gsber
UNION
select bukrs, ‘’ as gjahr, ‘’ as hkont, ‘’ as gsber, prctr,
sum(dmbtr) as dmbtr, sum(wrbtr) as wrbtr,
sum(dmbe2) as dmbe2, sum(dmbe3) as dmbe3, count( * ) as count
from bsis
GROUP BY bukrs, prctr
INTO CORRESPONDING FIELDS of TABLE @DATA(it_sum).

AMDP Code:
METHOD gl_groupset
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS
READ-ONLY
USING bsis.
et_out = select bukrs, gjahr, hkont, gsber, prctr,
sum(dmbtr) as dmbtr, sum(wrbtr) as wrbtr,
sum(dmbe2) as dmbe2, sum(dmbe3) as dmbe3, count( * ) as count
from bsis
GROUP BY GROUPING SETS ( ( bukrs, gjahr), (bukrs, hkont) , (bukrs,
gsber) , (bukrs, prctr ) );
et_out = APPLY_FILTER( :ET_OUT, :iv_where );
ENDMETHOD.

Along with GROUPING SET, there are two more options viz., ROLLUP and
CUBE. The ROLLUP(Col1, Col2, Col3) is equivalent to GROUPING SETS (
(Col1, Col2, Col3), (Col1, Col2), Col1). The CUBE(Col1, Col2, Cole) is
equivalent to GROUPING SETS ( (Col1, Col2, Col3), (Col1, Col2), (Col1,
Col3), (Col2, Col3), Col1, Col2, Col3). You can use the GROUPING_ID
function with GROUPING SETS, ROLLUP, or CUBE. It returns an integer
value that groups a set of the returned row.
Window Function
The window function is one of the advanced techniques used in SQL.
Aggregate functions return a single scalar value on a set of records.
Window functions operate on a set of rows called ‘window’ and return a
single value for the underlying query somehow related to the current row.
This means that the window functions allow you to divide the result sets of
a query into groups of rows called window partitions. The window
function is executed by the OVER clause and you can express the query
expression using the PARTITION BY clause. The partitioning is horizontal
so that data sets of rows are mapped to individual partitions. Note that the
rows are not collapsed (as in the aggregate functions). Use cases of window
functions include running totals, moving averages, and identifying gaps.

The window function provides additional information over the existing

query result. The performance of the window function is always better
because the computation is based on the result set. The window function
does not allow the WHERE clause to add further business logic. It allows
only the ORDER BY clause and the PRECEDING clause (to pick how many
rows from the current row). The window function is very powerful and
you cannot avoid possible subqueries (on the SELECT clause). You can see
the list of windows functions other than aggregate functions in the
following table.

Function Comments
RANK Ranking function
DENSE_RANK Ranking; no skipping ranking number
PERCENT_RANK Calculates the percentage of values that are either less
or greater than the current record value
LAG Information from previous partition record
LEAD Information from the following partition record
ROW_NUMBER The row number of the partition
NTILE Nth title of the partition record
FIRST_VALUE Returns value of the first element of an expression
LAST_VALUE Returns value of the last element of an expression
Row Number

The window ranking function enables you to sequence the number (row)
within a partition of the result. Within the partition, the first row is
assigned as 1. Note that the row number is a non-deterministic function
among tie values. The sequence order is based on the order within the
partition by clause (OVER window).

Scenario: Consider the scenario in which you need to delete duplicates

based on the first four columns from the internal table. The ABAP
statement DELETE ADJACENT DUPLICATES keeps the first record and
deletes the following duplicate records. Before deleting, you need to sort
the internal table based on the columns used for comparison.

AMDP Code: The following code snippet deletes adjacent duplicates by

comparing the fields company code, fiscal year, and G/L account.
et_out = SELECT * FROM
( select *, row_number( ) OVER
( PARTITION by bukrs, gjahr, hkont ) as RN
from :lt_tab )
where rn = 1;

Rank

SQL Rank function is one of the aggregation functions and it returns the
rank for individual fields as per categorization or grouping. It is also
known as Window Functions. There are multiple rank functions supported
by SAP SQL Script including ROW_NUMBER, RANK, DENSE_RANK, and
NTILE.

Scenario: Consider the scenario in which you need to rank the sales for each
product name. Assume that there is only one product name and
corresponding sales. The required output is to list out the product name,
type, sales, and rank with the selection criteria. The use case is to list out all
the top 3 sales for each product category.

ABAP Code:
SELECT ProdName, Type, Sales
FROM ProductSales
ORDER BY ProdName, Type
INTO table lt_prod;
sort lt_prod by sales desc.
 LV_RANK = 1.
LV_PREV_SALES = 0.
LOOP AT lt_prod INTO data(lv_prod).
Move-corresponding lv_prod TO lv_prod1.
IF lv_prev_sales > lv_prod-sales.
Lv_rank = lv_rank + 1.
ENDIF.
Lv_prod1-rank = lv_rank.
APPEND lv_prod1 TO lt_prod1.
ENDLOOP.

AMDP Code:

METHOD RANK_PRODUCT BY DATABASE PROCEDURE FOR HDB LANGUAGE

SQLSCRIPT OPTIONS READ-ONLY.
ex_out = SELECT ProdName, Type, Sales,
RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC
) AS Rank
FROM ProductSales
ORDER BY ProdName, Type;
ENDMETHOD.

The DENSE_RANK function returns the ranks like the RANK() function but
with a small difference. The rank function skips the ranking order when
two records have the second rank than the following rank will be fourth.
But in the DENSE_RANK function, the rank is always sequential (no
jumping). You can see the following to show the difference between the
rank and the dense rank.

AMDP Code:
METHOD RANKING_PRODUCT BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
ex_out = SELECT ProdName, Type, Sales,
RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC ) AS
Rank,
DENSE_RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC )
AS DRank,
ROW_NUMBER() OVER ( PARTITION BY ProdName ORDER BY Sales DESC
) AS Rowid
 FROM ProductSales
ORDER BY ProdName, Type;
ENDMETHOD.

LAG and LEAD Function

The lead function is used to access the data from subsequent rows along
with the current data row. The lag function is used to access the data from
previous rows along with the current data row. Consider the scenario, you
need the list of total sales per sales organization, fiscal year, and a quarter
along with the previous quarter's total sales.

ABAP Code:
Select sales org, fiscal year, case when period to quarter, sum(amount)
FROM TABLE1 group by sales org, fiscal year.
LOOP AT lt_sales INTO FIELD-SYMBOL(<fs_sales>).
DATA(lv_tabix) = sy-tabix - 1.
If lv_tabix > 0.
READ TABLE lt_sales INTO DATA(lv_psales) WITH INDEX lv_tabix.
<fs_sales>-psales = lv_psales-sales.
Endif.
ENDLOOP.
AMDP Code:
SELECT
employee_id,
employee_name,
salary,
lead(salary, 1, 0) over (order by salary desc) as next_highest_salary
FROM employees;

Moving Average

Moving average and weighted moving average are time-series techniques

for analyzing the trends in the data. The moving average is also known as
rolling average or running average and it is calculated as the mean of the
current and specified preceding values for the point in time. Moving
weighted average uses greater weightage for a recent point and past data
are assigned with less weightage. You can use the window functions OVER
on the last 7 proceeding rows. SAP HANA uses the weight factors with the
following algorithm.
weight(<i>) = 2 * (<n> + 1 - <i>) / (<n> * (<n> + 1))
Scenario: Consider the data of daily sales and you need to find the 7-day
moving average and 7-day moving weighted average sales. The
assumption is that the table will have everyday sales records.

ABAP Code:
SELECT salesdt, sales INTO CORRESPONDING FIELDS of lt_sales
FROM ZSD_SALES_020
WHERE salesdt in s_dt.

SORT lt_sales BY salesdt.

LOOP AT lt_sales ASSIGNING FIELD-SYMBOL(<fs_sales>).
APPEND <fs_sales> TO lt_sales7.
IF LINES( lt_sales7 ) = 1.
<fs_sales>-mavg7 = <fs_sales>-price.
<fs_sales>-wmavg7 = <fs_sales>-price.
CONTINUE.
endif.
IF LINES( lt_sales7 ) > 7.
DELETE lt_sales7 INDEX 1.
ENDIF.
<fs_sales>-mavg7 = REDUCE #( INIT tot = 0 FOR lv_sales IN lt_sales7 NEXT tot = tot
+ lv_sales-price ).s
<fs_sales>-mavg7 = <fs_sales>-mavg7 / lines( lt_sales7 ).
DATA(lv_totwt) = lines( lt_sales7 ) * ( lines( lt_sales7 ) + 1 ). “Total weight
clear: <fs_sales>-wmavg7.
LOOP AT lt_sales7 INTO DATA(lv_sales7).
lv_wt = 2 * ( lines( lt_sales7 ) + 1 - sy-tabix ) / lv_totwt.
<fs_sales>-wmavg7 = <fs_sales>-wmavg7 + lv_wt * lv_sales7-price.
ENDLOOP.
ENDLOOP.

AMDP Code:
SELECT salesdt, sales,
AVG(sales) OVER ( ORDER BY salesdt ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW ) AS mavg7,
WEIGHTED_AVG(sales) OVER (ORDER BY salesdt ROWS BETWEEN 6
PRECEDING AND CURRENT ROW) AS wmavg7
FROM ZSD_SALES_020;
et_sales = APPLY_FILTER( :et_sales, :iv_where ).

You see the sample data on the moving average data.

S No. Date Sales 7 Day
7 Day
Moving
Weighted
Average
Moving
Average
1 20221201 23000.00 23000.00 23000.00
 2 20221202 12000.00 17500.00 15666.66
3 20221203 25000.00 20000.00 20333.33
4 20221204 10000.00 17500.00 16200.00
5 20221205 11000.00 16200.00 14466.66
6 20221206 26000.00 17833.33 17761.90
7 20221207 28000.00 19285.71 20321.42
8 20221208 32000.00 20571.42 23500.00
9 20221209 12000.00 20571.42 21357.14
10 20221210 11000.00 18571.42 18964.28
11 20221211 9000.00 18428.57 16571.42
12 20221212 27000.00 20714.28 18714.28

Binning Function

Binning is a technique that allows you to put similar datasets into bins. You
can assign a bin number to each row based on the partitioning of an input
set into disjoint sets. SAP HANA SQLScript supports multiple binning
functions based on the count, width, tile count, and standard deviation.
AMDP Code:
METHOD get_binning
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING ZT_SALES.

et_sales =
SELECT salesdt, sales,
BINNING(VALUE => sales, BIN_COUNT => 4) OVER () AS bin1,
BINNING(VALUE => sales, BIN_WIDTH => 4) OVER () AS bin2,
BINNING(VALUE => sales, TILE_COUNT => 4) OVER () AS bin3,
BINNING(VALUE => sales, STDDEV_COUNT => 4) OVER () AS bin4
FROM ZT_SALES ; -- Add your where conditions

ENDMETHOD.

Note that OVER has to partition by clause. The binning computation is on

the whole dataset.

Interpolation

You can interpolate based on the percentile of the distribution. You can use
SAP HANA functions PERCENTILE_CONT and PERCENTILE_DISC to
interpolate the value based on the series. The basic rule is that if value X is
at the nth percentile, then X is greater than n% of the values.
Scenario: Consider the dataset with a list of daily sales values for each
department. You need to find the 85% value and 85% percentile of the data
values for each department. The value represents the exact value and the
percentile represents the value on or after the 85% value in the data set.
ABAP Code:

sort lt_salesd by dept salesdt.

LOOP AT lt_salesd INTO DATA(lv_salesd) “Group by department
GROUP BY ( dept = lv_salesd-dept ) ASCENDING ASSIGNING FIELD-
SYMBOL(<fs_group>).
REFRESH: lt_salesd1.
lv_min = 999999999. “Compute min and max at department level
lv_max = 0.
LOOP AT GROUP <fs_group> ASSIGNING FIELD-SYMBOL(<fs_salesd>).
APPEND <fs_salesd> TO lt_salesd1.
if lv_min > <fs_salesd>-sales.
lv_min = <fs_salesd>-sales.
endif.
if lv_max < <fs_salesd>-sales.
lv_max = <fs_salesd>-sales.
endif.
ENDLOOP.
DATA(lv_diff) = lv_max - lv_min.
lv_pd1 = lv_min + lv_diff * lv_perc. “Get 85th percentile value
SORT lt_salesd1 by sales.
LOOP at lt_salesd1 INTO DATA(lv_salesd1) WHERE dept = <fs_group>-dept
AND sales >= lv_pd1.
lv_pd2 = lv_salesd1-sales. “Get 85th percentile value in the data set
EXIT.
ENDLOOP.
CLEAR lv_salesp.
lv_salesp-dept = <fs_group>-dept.
lv_salesp-pd1 = lv_pd1.
lv_salesp-pd2 = lv_pd2.
APPEND lv_salesp TO lt_salesp.
ENDLOOP.

AMDP Code:
METHOD interpolate
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
lt_sales = SELECT salesdt, dept, sales,
PERCENTILE_CONT (:iv_percent) WITHIN GROUP ( ORDER BY sales )
OVER ( PARTITION BY dept ) AS pd1,
PERCENTILE_DISC (:iv_percent) WITHIN GROUP ( ORDER BY sales )
OVER ( PARTITION BY dept ) AS pd2
 FROM :it_sales;
et_sales = SELECT distinct dept, pd1, pd2 FROM :it_sales;
ENDMETHOD.

Linear approximation is a technique to interpolate certain points that are in

real values of some function. SAP HANA provides a LINEAR_APPROX
function to find and replace missing values by interpolating between
adjacent NULL values. The function operates on the entire series and
produces a new series that replace the missing values with approximated
values.

Scenario: Consider you have the list of sales values and corresponding
profit values as it is in the table. You need to interpolate the profit value for
the new sales value.

Sales Profit
1,000,000 200,000
2,000,000 300,000
3,000,000 400,000
4,000,000 500,000
5,000,000 600,000
6,000,000 ?

AMDP Code:
METHOD linearapprox
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
lt_sales = SELECT sales, profit,
LINEAR_APPROX ( profit, ‘EXTRAPOLATION_LINEAR’) OVER ( ORDER
by sales ) as approx
FROM :it_sales;
et_sales = SELECT sales, COALESCE(profit, approx) as val
FROM :it_sales;
ENDMETHOD.

Note that window functions other than ROW_NUMBER,

PERCENTILE_DISC, PERCENTILE_CONT, and the window aggregate
functions must have an ORDER BY clause in the OVER clause.
Random Partition

Splitting datasets into training, test, and validation datasets is common

practice in data science projects. SAP HANA SQLScript provides a random
partition function RANDOM_PARTITION to divide the datasets (internal
table) into three different datasets (training, validation, and test sets). The
split of datasets is random. The partition function returns the set number
for each record (0 – no set is assigned, 1 – training set, 2 – validation set, and
3 – test set). The stratified partitions are supported via the window
function PARTITION BY clause.

The function parameters include training set size, validation set size, test set
size, and seed. When the total of all sizes is less than one, then the sizes are
expressed as fractions of the total input size. When the sizes are greater
than or equal to one, then it represents the actual size of the data set. The
random seed is used to initialize the pseudo-random number generator.
When set to zero, the seed is initialized with the current time.
AMDP Code
METHOD random_partitioning
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.

et_sales =
SELECT salesdt, sales,
( CASE RANDOM_PARTITION(0.5, 0.2, 0.3, 0) OVER (ORDER BY sales)
WHEN 1 THEN 'Training'
WHEN 2 THEN 'Validation'
WHEN 3 THEN 'Test'
ELSE 'Not Assigned' END ) AS partition
FROM :it_sales;

ENDMETHOD.
Advanced Topics
The chapter intends to cover the advanced topics you can use in your
AMDP method implementation. A few of the techniques may or may not
apply to ABAP development. The chapter covers CDS table functions,
dynamic programming, hierarchy functions, and spatial functions.
Dynamic programming allows you to generate dynamic SQL. Also, the
chapter details the parallel processing and mail merge of the custom UDF.
The series generation functions enable you to populate the series of data
values within a given interval. The chapter explains a few series of
generation functions with examples. SAP HANA includes a multilayered
spatial engine and spatial methods. The geographical calculation requires
complex algorithms to handle various geo coordinates. SQLScripts
provides multiple spatial functions. The chapter discusses a few spatial
functions with examples.

CDS Table Function

SAP HANA provides a virtual data model (VDM) feature that allows you to
create a virtual representation of data from multiple sources, such as SAP
ERP and non-SAP systems, and access it as if it were all stored in a single
location. The CDS views provide a virtual data model where it uses basic
SQL functions, joins, and unions with the association within SAP
Ecosystem. Note that the CDS cannot support complex computations like
ABAP programming. The CDS table function uses the power of
programming to provide complex business solutions. The CDS Table
function is a part of the CDS data definition. It uses the AMDP class
method as a data source. The CDS table function can be consumed directly
by CDS views and ABAP OpenSQL. Note that the CDS table function
cannot have any sqlViewName. But you can expose it in another CDS view
with the SQL view name.
The main purpose of the CDS table function is to expose the result of the
class method to a CDS view. The table function behaves like a table and it
can be consumed in any of the CDS views and ABAP OpenSQL. It
simplifies the data model of complex logic. Another advantage is that you
can debug the results using an AMDP debugger. The table function opens a
lot of gates in data modeling. The table function supports the parameters.
Each CDS table function has two components: the actual CDS entity of the
table function and the AMDP method implementation. The method must
be declared as a table function (user-defined table function). This is a part
of code pushdown capabilities in ABAP CDS. The CDS table function can
have parameters that can be passed to the AMDP method. You can
implement the implicit client handling on the CDS entity using the
annotation @Environment.systemField:#CLIENT.

Scenario: Consider the situation that you need to get a list of obligations,
adjustments, and consumption by period for a given PO. The obligation is
the initial amount created by PO. The adjustments refer to the change in the
PO amount and consumption is the invoiced amount.

CDS view definition:

@ClientDependent: true
define table function DEMO_CDS_PO_SUMMARY
with parameters @Environment.systemField: #CLIENT
clnt:abap.clnt,
ebeln:s_ebeln
returns { client:s_mandt;
PO: ebeln;
POItem: ebelp;
FiscalYear : gjahr
Period : perio
Obligation :oblig
Adjustment :adj
Consumption: consum; }
implemented by method
CL_DEMO_AMDP_FUNCTIONS=> DEMO_CDS_PO_SUMMARY;

Note that you need to use uppercase for AMDP class and method name.
AMDP Code:
CLASS-METHODS get_po_summary
FOR TABLE FUNCTION DEMO_CDS_PO_SUMMARY.

METHOD DEMO_CDS_PO_SUMMARY
BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
 OPTIONS READ-ONLY
USING scarr spfli.
RETURN SELECT sc.mandt as client,
sc.carrname, sp.connid, sp.cityfrom, sp.cityto
FROM scarr AS sc
INNER JOIN spfli AS sp ON sc.mandt = sp.mandt AND
sc.carrid = sp.carrid
WHERE sp.mandt = :clnt AND
sp.carrid = :carrid
ORDER BY sc.mandt, sc.carrname, sp.connid;
ENDMETHOD.

How to use it in ABAP OpenSQL

SELECT *
FROM demo_cds_po_summ( ebeln = @lv_ebeln)
INTO TABLE @DATA(result)
##db_feature_mode[amdp_table_function].

AMDP Mesh

ABAP mesh is a development paradigm in the ABAP platform where you

can use a special data type and its main components are based on the
internal tables. ABAP mesh is some kind of mimic of the CDS views with
its associations' concept but with an ABAP-specific syntax. The ABAP
mesh is a record of multiple internal tables that are joined by associations.
You can access the MESH variable like the path expression used in the CDS
view consumption in the ABAP code.

The following sample code demonstrates a simple CDS review with an

association and how it is consumed in ABAP code.

@AbapCatalog.sqlViewName: 'ZCV_CDS_SO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Order Details'
define view ZC_SD_SO_DET
as select from ZI_SALESORDER_HEADER as head
association [1..*] to ZI_SALESORDER_ITM as _Items
on $projection.vbeln = _Items.vbeln
{
key SalesOrder,
 CreationDate,
vbtyp,
OrderType,
Amount,
Currency,
_Items
}

In CDS, the path expression is a way of navigating through the data model
to select specific data using the associations. Path expressions and filtered
associations make it easier to read the CDS View definitions, as well as the
SELECT statements accessing the views. In OpenSQL, the path expression
is composed of a sequence of path segments, which are separated by the
slashes '\'.

TRY.
SELECT SalesOrder, CreationDate, OrderType, Amount, \_Items-matnr
FROM ZC_SD_SO_DET
WHERE SalesOrder in so_vbeln
INTO TABLE @DATA(lt_so).
cl_demo_output=>display(lt_so).
CATCH.
ENDTRY.

The path expression can be used in both the SELECT and FROM classes.
You can do the same with the ABAP mesh without the CDS definition.
First, you need to define all internal tables and their associations. Once you
populate the internal tables in the ABAP mesh, you can use the path
expression.

types:
t_orders TYPE SORTED TABLE OF vbak WITH UNIQUE KEY node_key .
types:
t_items TYPE SORTED TABLE OF vbap WITH UNIQUE KEY node_key
with NON-UNIQUE SORTED key parent_key
COMPONENTS parent_key.
types:
BEGIN OF MESH t_sales_order,
items TYPE t_items
ASSOCIATION _products TO products ON node_key = product_guid,
orders TYPE t_orders
ASSOCIATION _items TO items ON parent_key = node_key USING
KEY parent_key
 END OF MESH t_sales_order .
DATA: lt_so TYPE t_sales_order.

There is no big difference between implementing the MESH from the ABAP
code or the AMDP code. The complexity of the structure and complex
ways of populating the internal table may determine whether you can use
ABAP code or AMDP code. Also, the parallel processing of AMDP can
improve performance. You can see the demo program
DEMO_AMDP_MESH. The following code snippet demonstrates the path
expression used in the ABAP mesh.

TRY.
DATA(lt_item) = lt_so~orders\items[ matnr = p_matnr].
CATCH.
ENDTRY.

Dynamic Programming
ABAP is a typed programming language where you can define the type of
data type and it will be known at compile-time or runtime. ABAP 7.4
features provide an inline function that avoids the explicitly typed
declaration. The runtime type services (RTTS) are used to handle types at
runtime. In ABAP, the type description object uses the type description
class and its attributes to describe the technical properties of the type. The
RTTS has two methods viz., RTTC (Runtime Type Creation) and RTTI
(Runtime Type Identification). You can execute the dynamic SQL
statement using EXEC and EXEC IMMEDIATELY statements. These
statements can be used both in ABAP and AMDP SQL Script. ABAP
manages the implementation of the AMDP database procedures, but its
contents and security are not part of ABAP’s responsibility. Whenever any
dynamic parts incur any risks that are unknown in ABAP, they cannot be
checked.

In the AMDP method, the dynamic programming is referred to the dynamic

SQL. The basic idea of dynamic SQL is to allow you to construct the SQL
statement during the execution of the AMDP method. SAP recommends
avoiding dynamic programming in AMDP code. When you have any other
alternative to dynamic SQL, then you can use it instead of dynamic SQL.
The disadvantages include:
 • Dynamic SQL is recompiled every time it is executed
• Limited opportunities for optimizations
• Possible SQL injection issues

SQLScript supports dynamic SQL using the function APPLY_FILTER and

the operator EXEC or EXEC IMMEDIATE. The APPLY_FILTER function
enables you to set dynamic conditions on your results set. The function is
already discussed in the solution on how to pass the select options to the
AMDP method. Note that it can cause SQL injection. With EXEC and
EXEC IMMEDIATE statements, you can parameterize the dynamic SQL.
You can transfer parameters to the dynamic SQL and assign the result to a
table-type variable. The input parameters can be applied INTO the FROM
clauses and WHERE conditions.

DECLARE lv_cnt INTEGER;

EXEC ‘SELECT COUNT(*) FROM ZT_TAB WHERE col1 = ?’ INTO lv_cnt USING
:iv_col1;

--you can pass the internal table

lt_tab = SELECT col1, col2 from ZT_TAB
EXEC IMMEDIATE ‘SELECT COUNT(*) FROM :lt_tab ‘ INTO lv_cnt USING :lt_tab;

The dynamic SQL statements can be applied into the SELECT clause.
DECLARE cname1 VARCHAR(20) = ‘ZCOL1’;
DECLARE cname1 VARCHAR(20) = ‘ZCOL2’;
DECLARE lv_min INT ;
DECLARE lv_max INT;
EXEC ‘SELECT MAX(‘ || ?|| ‘) , MIN(‘ || ?|| ‘) FROM ZT_TAB WHERE col1 = ?’
INTO lv_max, lv_min USING :cname1, :cname2;

Scenario: Need to get the list of archived purchase requisitions for the given
info structure. The archived index table is dynamically generated for each
client. The following code snippet provides the dynamic programming
concept. Note that there is no big difference between the ABAP code and
the AMDP code for the selection. When you want to process a lot of
archived data, then AMDP code provides a good edge on performance.
AMDP Code:

METHOD get_arch_prs BY DATABASE PROCEDURE

FOR HDB LANGUAGE SQLSCRIPT
USING aind_str1 aind_str2.

declare v_i INTEGER;

 lt_prtabname =
SELECT DISTINCT str1.object as object, str2.archindex as archindex,
str2.gentab as gentab
FROM aind_str2 AS str2
INNER JOIN aind_str1 AS str1 ON str1.archindex = str2.archindex
WHERE str2.archindex = :iv_info_str --input parameter
'SAP_DRB_MM_EBAN'
AND str2.active = 'X';

lt_out = select gentab as banfn from :lt_prtabname;

for v_i in 1..RECORD_COUNT(lt_prtabname) do
DECLARE tv TABLE ( banfn VARCHAR( 10 ) );
DECLARE v_name VARCHAR( 30 );
v_name = :lt_prtabname.gentab [ v_i ];
EXEC 'SELECT banfn FROM ' || :lt_prtabname.gentab[ v_i ] INTO tv;
lt_out = select * from :lt_out union select * from :tv; -- union from previous result if any
END FOR ;

et_out = select * from :lt_out where banfn <> '';

ENDMETHOD.

Parallel Processing

In ABAP programming, you can implement parallel processing by

executing the function module parallelly with START WITH NEW TASK
and DESTINATION IN GROUP. You can perform a subroutine at end of
the task to handle parallelly processed data. Also, you can use the function
module SPTA_PARA_PROCESS_START_2 SAP to handle the parallel
processing. Refer to the program SPTA_PARA_DEMO_1 for further
information.

The column-based table enables you to execute the operations in parallel

using multiple processor cores. The implicit parallelization has been
applied for distinct read-only procedures and table variable assignments.
In this section, you can see more explicit parallel processing at the
SQLScript level so that you can use it in the AMDP method. HANA
database provides multiple ways to handle the parallel processing in SQL
execution or stored procedure/functions parallelly. One very common
way is that execute the explicit SQL statements or stored procedures
parallelly using the PARALLEL EXECUTION statement.
 BEGIN PARALLEL EXECUTION.
<sql statement>
<sql statement>
END;

You can exploit the parallelization by avoiding unnecessary dependencies

between two separate SQL statements. When the SQL statements in the
AMDP method are independent of each other, then it is also possible to
execute the statements in parallel.

Map Merge

Map Reduce function is a programming model introduced by the Hadoop

framework that enables the easy development of scalable parallel
applications for processing big data on large clusters of commodity
machines. It has two components mapper function and reduce method.
The mapper function performs filtering and sorting and the reduce method
performs a summary operation. SAP HANA map reduce operator uses the
user-defined functions for both mapper and reduce methods. The map-
reduce function has three parameters viz., the input table, the mapper
function, and reduce function. The mapper function uses the data from the
input table record as its input record and performs selection, filtering, and
sorting and passes it to the reducer function to summarize the result.
The map merge is a specialization on the map-reduce operator. The
MAP_MERGE function is used to apply the mapper function for each
record in the input table and unite all intermediate result tables. The main
purpose of this function is to replace sequential FOR-LOOP and union
patterns with the parallel operator. The mapping arguments for the
mapping functions can be passed from the input table.

Scenario: Consider that there is a complex UDF function already written,

and it is parameterized with the PO number as input and returns the list of
FMIOI entries. The following code snippet explains how to execute the
UDF function parallelly in the AMDP method. The following example is a
simple example to demonstrate the map merge function.

AMDP code:
CLASS zcl_amdp_merge DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
 PUBLIC SECTION.
TYPES:
BEGIN OF TY_po,
po type ekko-ebeln,
END OF ty_PO,
ty_po_tbl TYPE TABLE OF ty_po.

TYPES: BEGIN OF po_fmioi_info,

ebeln TYPE ekko-ebeln,
bukrs type ekko-bukrs,
gjahr TYPE fmioi-gjahr,
perio TYPE fmioi-perio,
btart TYPE fmioi-btart,
amount TYPE fmioi-trbtr,
END OF po_fmioi_info,
po_fmioi_tbl TYPE TABLE of po_fmioi_info WITH NON-UNIQUE KEY
ebeln.

INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS:

merge_fm
IMPORTING
VALUE(iv_po) TYPE ebeln
RETURNING
VALUE(et_fmioi) TYPE po_fmioi_tbl,

seq_merge
IMPORTING
VALUE(it_po) TYPE ty_po_tbl
EXPORTING
VALUE(et_fmioi) TYPE po_fmioi_tbl ,

map_merge
IMPORTING
VALUE(it_po) TYPE ty_po_tbl
EXPORTING
VALUE(et_fmioi) TYPE po_fmioi_tbl .

PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_amdp_merge IMPLEMENTATION.

-- Database function returns the FMIOI entries for a given PO number. This is to
demonstrate
-- map merge
METHOD merge_fm BY DATABASE FUNCTION FOR HDB LANGUAGE
SQLSCRIPT
OPTIONS READ-ONLY USING EKKO FMIOI.

return select ekko.ebeln, ekko.bukrs, fmioi.gjahr AS gjahr, fmioi.perio,

fmioi.btart, sum( trbtr ) as amount
FROM EKKO
INNER JOIN FMIOI ON fmioi.refbn = ekko.ebeln AND ekko.mandt =
fmioi.mandt
WHERE ekko.ebeln = :iv_po
AND ekko.mandt = SESSION_CONTEXT( 'CLIENT' )
GROUP BY ekko.ebeln, ekko.bukrs, fmioi.gjahr , fmioi.perio, fmioi.btart;

ENDMETHOD.
--Sequence method to call database function for each po values in the input table
METHOD seq_merge BY DATABASE PROCEDURE FOR HDB LANGUAGE
SQLSCRIPT
OPTIONS READ-ONLY USING
ZCL_AMDP_MERGE=>MERGE_FM .

DECLARE lv_cnt INT;

DECLARE lv_po VARCHAR( 10 );
FOR lv_cnt IN 1 .. record_count( :it_po ) DO
lv_po = :it_po.po[ :lv_cnt ];
et_fmioi = SELECT * from :et_fmioi
UNION SELECT * FROM "ZCL_AMDP_MERGE=>MERGE_FM" (
iv_po => lv_po );
END FOR;

ENDMETHOD.
--PARALLEL PROCESSING for the records in the PO internal table. You can see
a big difference based
--on the table. The complexity of functions and the size of the internal table
determine the
--performance

METHOD map_merge BY DATABASE PROCEDURE FOR HDB LANGUAGE

SQLSCRIPT
OPTIONS READ-ONLY USING
ZCL_AMDP_MERGE=>MERGE_FM .

et_fmioi = MAP_MERGE ( :it_po, "ZCL_AMDP_MERGE=>MERGE_FM" (

:it_po.po ) );

ENDMETHOD.

ENDCLASS.
The map-reduce function cannot be implemented in the AMDP function
due to its restriction of passing the table type in the IMPORT parameter.
The import parameter allows only elementary data types only in the
database function implementation.

Series Generation

Series data is a sequence of data points, successive measurements made

over a time interval. Series data is used in statistics, pattern recognition,
econometrics, finance, and any domains of applied sciences. SAP HANA
provides efficient storage for multivariate series with both equidistant and
non-equidistant time. HANA extended column-oriented in-memory
structures to provide efficient processing for series data. You can store the
series data in a column table. You need to use the SERIES clause while
creating the column table. SAP provides several built-in SQL functions to
work with series data.
• SERIES_GENERATE – Generate a complete series
• SERIES_DISAGGREGATE – Move from coarse units to finer
• SERIES_ROUND – Converts a single value to a coarser resolution
• SERIES_PERIOD_TO_ELEMENT – Converts a timestamp in series to its
offset
• SERIES_ELEMENT_TO_PERIOD – Converts an integer to the associated
period

SQLScript provides several analytical functions for series data including

CORR (Correlation), MEDIAN, and LINEAR_APPROX (interpolating the
data points) as a part of the aggregation function. A series generator
generates a series of numbers within a given interval without any loop. Do
not confuse it with database sequence. SQLScript provides a complement
of SERIES_GENERATION functions to a complete series table. A few series
generator functions are:
• SERIES_GENERATE_DATE – Generate the series of dates for a given range
of dates.
• SERIES_GENERATE_TIMESTAMP – Generate the series of timestamps for
a given range of date times.
• SERIES_GENERATE_INTEGER - Generates the series of integers for a
given range and increment.
 • SERIES_GENERATE_DECIMAL – Generates the series of decimals for a
given range.

All these functions return the columns including start, end, element, and
fraction information. It is a common use case in business intelligence
queries, you need to expand the range of dates. You can see the details of
the columns in the following table.

Column Data Type Description

GENERATED_PERIOD_START Period Type Start of the period
GENERATED_PERIOD_END Period Type End of the period
ELEMENT_NUMBER BIGINT Element number within the series
FRACTION_OF_MIN_MAX_RAN DOUBLE Ratio length of the period
GE

The scenario is that you need to create periods between the dates with the
assumption is that
METHOD test_generate_period BY DATABASE PROCEDURE FOR HDB LANGUAGE
SQLSCRIPT OPTIONS READ-ONLY.
ex_out = SELECT TO_DATS(GENERATED_PERIOD_START) as from_date,
TO_DATS(add_days( generated_period_end, -1 )) as to_date
FROM SERIES_GENERATE_DATE( 'INTERVAL 1 MONTH', :from_date, :to_date
);
ENDMETHOD.

You can see the result as follows:

The disaggregating data refers to breaking down the aggregate data into
small subpopulations to elucidate underlying trends and patterns. The
disaggregate functions generate a complete series table with rows
disaggregated into defined partitions. A few series disaggregate functions
are:
• SERIES_DISAGGREGATE_DATE
• SERIES_DISAGGREGATE_TIMESTAMP
• SERIES_DISAGGREGATE_DECIMAL

The disaggregate function returns the following columns.

Column Data Type Description
SOURCE_PERIOD_START Period Type Source start of the period
SOURCE_PERIOD_END Period Type Source end of the period
GENERATED_PERIOD_START Period Type Start of the period
GENERATED_PERIOD_END Period Type End of the period
ELEMENT_NUMBER_IN_SOURCE_ BIGINT Element number within the
PERIOD source
ELEMENT_NUMBER_IN_GENERA BIGINT Element number within the
TED_SERIES series
FRACTION_OF_SOURCE_PERIOD DOUBLE Ratio length that the source
period covered
FRACTION_OF_MIN_MAX_RANG DOUBLE Ratio length of the period
E

The following code snippet uses the function

SERIES_DISAGGREGATE_TIMESTAMP to generate the timestamp series.

lt_series =
SELECT SOURCE_PERIOD_START, SOURCE_PERIOD_end,
generated_period_start, generated_period_end
FROM SERIES_DISAGGREGATE_TIMESTAMP(
'INTERVAL 1 MONTH',
'INTERVAL 1 DAY',
TO_DATE('2022-10-01'),
TO_DATE('2023-11-01'));

See the results in the Debugging session.

The Series round function is to round to a coarser granularity using the
specified rounding settings. The SERIES_ROUND function is allowed in
the GROUP BY clause so that you can do aggregate functions.

JSON/XML
JSON and XML formats are commonly used in SAP for data exchange.
JSON (Javascript Object Notation) format is a lightweight standard text-
based format for representing structured data on Javascript object syntax.
XML (Extensible Markup Language) is a widely used text-based format for
representing structured information on plain text. ABAP provides several
tools to handle JSON and XML data. First, we can start with how can you
convert an internal table into JSON and XML format. The first code
snippets are representing how can you create JSON strings using ABAP
code and AMDP method.

ABAP Code:
lv_json_out =
/ui2/cl_json=>serialize(
data = lt_test
compress = abap_true pretty_name = /ui2/cl_json=>pretty_mode-camel_case
).

AMDP Code:
et_json = select * from :lt_test for JSON ;
The second code snippet explains how to generate XML output from an
internal table to XML format for both ABAP and AMDP codes.
ABAP Code

DATA xml_string TYPE string.

DATA(lo_ixml) = cl_ixml=>create( ).
DATA(lo_document) = ixml->create_document( ).
CALL TRANSFORMATION id SOURCE text = `XML Header`
numbers = lt_test
RESULT XML lo_document.
ixml->create_renderer( document = lo_document
ostream = io_ixml->create_stream_factory(
)->create_ostream_cstring(
string = xml_string )
)->render( ).

AMDP Code
et_xml = select * from :lt_test for
XML ('schemaloc'='http://thiscompany.com/schemalib',
'targetns'='http://thiscompany.com/samples');

SAP ABAP provides multiple tools to handle XML and JSON object
handling. XML reader tool (cl_sxml_string_reader) is used to handle both
XML and JSON data. The tools can be used to parse the JSON data and can
be converted into an internal table. The SQLScript enables you to do the
query on the JSON string to find scalar or record type values. In this
scenario, you are going to understand how to use the JSON_QUERY
function to handle the JSON string in SQLScript.

SAP HANA SQL supports JSON data management using the collection.
Like MongoDB, the collection object is used in SQLScript to store the JSON
entries. You can create the collection using the SQL statement CREATE
COLLECTION. The collection object does not have any fields. You can
insert the JSON entries directly into the collection. SQLScript provides you
with options to do a selection using the SELECT statement. In the select
clause and where clause, you can provide field names used in the JSON
structure. The fields can be used at any level of the collection. You can use
the WITH statement to join between two collections.
JSON Query

SAP SQLScript provides a few JSON functions to query on the JSON string.
The functions include JSON_VALUE, JSON_QUERY, and JSON_TABLE.
You can use the JSON_VALUE function to fetch the value of the path
expression on the JSON string. JSON_QUERY can fetch the values of the
path expression. Note that it returns a single value on the exact expression.
See the following code snippet, which tries to get the value of the path
express info.address[0].town (first address record’s town) and it returns
Allen Town as its value. The JSON_QUERY returns multiple values of
town (Allen Town, Fairfax). If you try same the path expression in
JSON_VALUE, you will get an empty value. The JSON_TABLE function is
another powerful function that can handle the JSON query on the column
table. The following code snippet demonstrates the power JSON_TABLE.

DECLARE lv_town VARCHAR( 30 );

DECLARE lv_towns VARCHAR( 200 );

SELECT JSON_VALUE('{"info":{"address":[{"town":"Allen
Town"},{"town":"Fairfax"}]}}', '$.info.address[0].town') INTO lv_town FROM DUMMY;
SELECT JSON_QUERY('{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}',
'$.info.address.town' ) INTO lv_towns FROM DUMMY;

Scenario: Consider the following JSON record for each PO Number. The
requirement is to get the list of all line items, part description, price, and
quantity along with the PO Number.

JSON String:
{
"PONumber": 1,
"Reference": "BSMITH-74635645",
"Requestor": "Barb Smith",
"User": "BSMITH",
"CostCenter": "A50",
"ShippingInstructions":
{
"name": "Barb Smith",
"Address":
{
"street": "100 Fairchild Ave",
"city": "San Diego",
"state": "CA",
 "zipCode": 23345,
"country": "USA"
},
"Phone": [{"type": "Office", "number": "519-555-6310"}]
},
"SpecialInstructions": "Surface Mail",
"LineItems": [
{"ItemNumber": 1, "Part": {"Description": "Basic Kit", "UnitPrice":
19.95, "UPCCode": 73649587162}, "Quantity": 7},
{"ItemNumber": 2, "Part": {"Description": "Base Kit 2",
"UnitPrice": 29.95, "UPCCode": 83600229374}, "Quantity": 1},
{"ItemNumber": 3, "Part": {"Description": "Professional",
"UnitPrice": 39.95, "UPCCode": 33298003521}, "Quantity": 8},
{"ItemNumber": 4, "Part": {"Description": "Enterprise",
"UnitPrice": 49.95, "UPCCode": 91827739856}, "Quantity": 8},
{"ItemNumber": 5, "Part": {"Description": "Unlimited",
"UnitPrice": 59.95, "UPCCode": 22983303876}, "Quantity": 8}
]
}

When you pass the JSON string, you can get all line items.
METHOD JSON_QUERY
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.

DECLARE lv_po VARCHAR( 10 );

et_out =
SELECT JT.*
FROM JSON_TABLE( :iv_json, '$'
COLUMNS (
RN FOR ORDINALITY,
PO VARCHAR( 10 ) PATH '$.PONumber',
NESTED PATH '$.LineItems[*]'
COLUMNS (
ITEM_NUMBER INT PATH '$.ItemNumber',
DESCRIPTION VARCHAR( 80 ) PATH '$.Part.Description',
QUANTITY INT PATH '$.Quantity',
PRICE DECIMAL PATH '$.Part.UnitPrice'
)
)
) AS JT;

ENDMETHOD.
NESTED PATH enables you to repeat the array in the path expression.
Multiple NESTED PATH returns the cartesian product of the path results.
You can see the result as follows:

Document Store

Document store enables you to work with SQL relational tables and
schema-less JSON collections. The feature eliminates a separate NO-SQL
document database. If you want to store JSON documents for long-term
processing or intermediate processing, the document store is highly
beneficial. You can create a collection object directly from ADT Eclipse or
SAP GUI. You need to have the HANA Database tool. Or you can use
AMDP or CL_SQL_STATEMENT ABAP object to create using the dynamic
SQL statement. Note that document store is an optional feature of the SAP
HANA procedure. Your system administrator needs to enable DocStore
per tenant database (by ALTER DATABASE <db> ADD ‘docstore’ ).

TRY.
DATA(o_sql) = NEW cl_sql_statement( ).
DATA(lv_statement) = |create collection json.qry1;|.
o_sql->execute_ddl( lv_statement ).
lv_statement = `insert into json.qry1 values({ "item1":1, "item2":2 } );`.
o_sql->execute_update( lv_statement ).
* Do the query
lv_statement = `select * from json.qry1 where item2 = 2 );`.
DATA(lv_statement) = |drop collection json.qry1;|.
o_result = sql->execute_query( lv_statement ).
o_result->set_param_table( REF #( lt_results ) ).
o_result->next_package( ).
cl_demo_output=>display( lt_results ).
o_sql->execute_ddl( lv_statement ). “ Drop collection
 CATCH cx_sql_exception INTO DATA(lo_cx).
cl_demo_output=>display( lo_cx->get_text( ) ).
ENDTRY.

XML Query

SAP HANA SQLScript provides a few XML functions to query the XML
string. The functions are:
• XMLEXTRACTVALUE function returns the XML value that matches with
XPath.
• XMLEXTRACT function returns an XML element for a given XPath query.
• XMLTABLE function creates the relational table from an XML string and
you can query on them like JSONTABLE. The returned value can be a
value or XML string.

The following code snippet demonstrates the XMLTABLE function. The

code processes the input XML string (iv_xml) and gets the name and price
of the item IN THE XML document.

METHOD XML_QUERY
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS
READ-ONLY.

DECLARE lv_po VARCHAR( 10 );

et_out =
WITH xmldoc AS ( SELECT :iv_xml as doc )
SELECT XM.*
FROM
XMLTABLE( ‘/root/item’ PASSING XMLDOC.DOC
COLUMNS (
ID INT PATH ‘@id’
NAME VARCHAR(50) PATH ‘name’,
PRICE DECIMAL(5,2) PATH ‘price’
) AS XM;
ENDMETHOD.

The iv_xml will have the following XML data.

'<root>
<item id="1">
<name>Item 1</name>
<price>10.99</price>
</item>
<item id="2">
<name>Item 2</name>
<price>5.99</price>
 </item>
</root>'

Hierarchy
Hierarchy data is referred to a set of data items that are related to each other
by hierarchical relationships. SAP HANA supports two types of
hierarchical data viz., parent-child and level-based hierarchical data. The
parent-child hierarchy is a simple hierarchy created between two
dimensions while level-based hierarchies can have multiple dimensions
with more complex drilling options. In the parent-child hierarchy, each
record references a parent record within the same table. The employee
manager is a good example of the parent-child hierarchy. In the level-based
hierarchy, each record contains complete individual path information
through the hierarchy. Common examples are address data (with country,
state, and city) and time-based data (Year, Month, Date).

Terminologies
Terminology Description
Hierarchy Hierarchy generator function result
Node Basic Element of Hierarchy
Parent Node has either one or no parent but has at
least one child node
Child All descendant node
Root The top one with a hierarchy parent rank is
zero
Orphan Node is not reachable from any node
Edge Relationship between two nodes

SAP HANA offers four hierarchy generator functions and four navigation
functions.
• HIERARCHY Generator function – Generating a hierarchy from a recursive
parent-child source data structure.
• HIERARCHY_SPANTREE Generator function – Uses the same source and
generates only a minimal spanning tree, which is useful when the source
data contains many multiple parent edges.
• HIERARCHY_TEMPORAL Generator function – Generating a hierarchy
from a recursive parent-child source for a specific time interval.
• HIERARCHY_LEVELED Generator function – Generates an analogous
output structure based on source data where individual source columns
 correspond to hierarchy levels and source rows define paths from a root to
a leaf node.
• HIERARCHY_ANCESTORS Navigation function – returns all ancestors of
a set of start nodes in the hierarchy.
• HIERARCHY_ANCESTORS_AGGREGATE – The function performs an
aggregate operation on the ancestors of a given node in a hierarchical table.
• HIERARCHY_DESCENDANTS_AGGREGATE – The function performs an
aggregate operation on the descendants of a given node in a hierarchical
table.
• HIERARCHY_DESCENDANTS – returns all descendants of a set of start
nodes in the hierarchy.
• HIERARCHY_SIBLINGS – retrieves all the siblings of a given node in a
hierarchical table.

Hierarchy Generator function

SQLScript enables the parent-child hierarchy using the FROM

HIERARCHY clause. Note that to generate the hierarchy data, your
hierarchy source (either database table or internal table) must have the
columns node_id and parent_id, and make sure that the data type of those
must be identical. The supported data types include numeric types,
character string types, date/time types, binary types, and spatial types.

SELECT * FROM HIERARCHY

( SOURCE SELECT id as node_id, manager as parent_id, name, salary from ZT_EMPL
);

The FROM HIERARCHY clause generates the following columns for

parent-child hierarchy along with the selected columns (name, salary).

Column Description
HIERARCHY_RANK The hierarchy rank of the node
HIERARCHY_PARENT_RANK Parent hierarchy rank
HIERARCHY_ROOT_RANK Root hierarchy rank
HIERARCHY_TREE_SIZE The tree size from the node
HIERARCHY_LEVEL The hierarchy level of the node
HIERARCHY_IS_CYCLE Whether hierarchy is cyclic
HIERARCHY_IS_ORPHAN Whether the node is an orphan or not

Similarly, you can generate the level-based hierarchy using the

HIERARCHY_LEVELED clause and it generates the following columns.
 Column Description
HIERARCHY_LEVEL_NAME The name of the level of hierarchy node
belongs
NODE_ID Current node identifier
PARENT_ID Parent node identifier

From the hierarchical data, you can traverse data either bottom-up or top-
down. You can use the hierarchical aggregate functions on the hierarchical
data. The analytical application uses the hierarchy to provide a high-level
overview with the option to drill down the branches. The hierarchy
generator function generates a hierarchy from a recursive parent-child
source of the data source. You can refer SAP hierarchy developer guide:
http://tinyurl.com/2272sf7w .

Scenario:

In this section, you can see how the hierarchy generator works for the WBS
element hierarchy as a use case. Consider the WBS hierarchy configuration
that has four levels of hierarchies. The hierarchy starts from the project and
continues with WBS elements (hierarchy of WBS elements based on
configuration), and continues to the Network data. When you define the
project and WBS element, you can see the WBS elements’ hierarchy data in
the table PRHI.

AMDP Code :

SAP SQLScript provides a hierarchy generation function to handle the

hierarchical data. Note that in this example, the CDS hierarchy is not
covered and the AMDP method uses the internal table to populate the
hierarchy data. Once you create the hierarchical data, you can use the data
as your data source for applying all hierarchy functions. In the sample code
snippet, it creates the hierarchy data from the database tables PRPI and
AUFK. To understand the process, complicated data logic is avoided.

* Output Type:
begin of ty_ps_out,
parent_id type CHAR12,
node_id type CHAR12,
hierarchy_level type I,
object TYPE char01,
end of ty_ps_out,
 ty_ps_out_tbl TYPE TABLE of ty_ps_out.
* Definition
CLASS-METHODS:
get_project_hierarchy
IMPORTING
VALUE(posnr) TYPE EDI_POSNR
EXPORTING
VALUE(et_out) TYPE ty_ps_out_tbl
RAISING cx_amdp_error.

* Implementation method
METHOD get_project_hierarchy BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
USING prhi aufk.

declare lv_proj VARCHAR( 8 );

select distinct psphi INTO lv_proj from prhi WHERE MANDT = 400 and posnr =
:posnr ;
lt_prhi =
select '00000000' as parent_id, cast(PSPHI as varchar(8)) as node_id, '00000000' as
posnr, psphi, '00000000' as up, 'P' as object from prhi
WHERE mandt = SESSION_CONTEXT(‘ CLIENT’)
and up = '00000000' and psphi = :lv_proj
UNION
select cast(PSPHI as varchar(8)) as parent_id, posnr as node_id, posnr, psphi,
cast(PSPHI as varchar(8)) as up, 'W' as object from prhi
WHERE mandt = SESSION_CONTEXT(‘ CLIENT’)
and up = '00000000' and psphi = :lv_proj
UNION
select up as parent_id, POSNR as node_id, posnr, PSPHI, up, 'W' as object from
prhi
WHERE mandt = SESSION_CONTEXT(‘ CLIENT’)
AND up <> '00000000' and psphi = :lv_proj
order by parent_id, posnr;

lt_prlist = select cast( parent_id as varchar( 12 ) ) as parent_id,

cast( node_id as varchar( 12 ) ) as node_id, psphi, up, object
from :lt_prhi
union
select node_id as parent_id, aufnr as node_id, psphi, node_id as up, 'N' as object
from :lt_prhi as p
INNER JOIN aufk ON AUFK.PSPEL = p.node_id;

lt_wbs_hier =
SELECT *
FROM HIERARCHY (
source ( select * from :lt_prlist ) SIBLING ORDER BY posnr ORPHAN ROOT
);
lt_desc = select * from HIERARCHY_DESCENDANTS(
 SOURCE :lt_wbs_hier
START WHERE node_id = :lv_proj
)
ORDER BY hierarchy_rank ;
et_out = select parent_id, node_id, hierarchy_level, object from :lt_desc;
ENDMETHOD.

The result of the AMDP:

Tips: You can use the distance so that you can provide how deep you can
go. You can get ancestors using the following code:

lt_ancs = select * from hierarchy_ancestors(

SOURCE :lt_wbs_hier
START WHERE node_id = :lv_proj
)
ORDER BY hierarchy_rank ;
Security
SAP HANA provides a wide range of security features to protect against
unauthorized access and data breaches. The features include user
authentication, role-based access control (authority-check), data encryption,
auditing, and network security. The ABAP database user requires the
privileges to handle HANA database access. You can see more details in
SAP OSS note 2101316. The ABAP code provides the authority-check
statement to validate the role-based access control of the user on the object.
In the CDS virtualization model, you can implement the authority check on
the CDS view. Data masking is a technique used by SAP HANA to mask
critical data other than authorized users. You can enable the masking at the
data modeling level. Also, you can use multiple techniques to mask data,
such as encryption, and hashing.

Hashing

Hashing is the process of scrambling raw data to the extent that it cannot
reproduce back to its original form. The primary applications of hashing
include password and sensitivity data hashes, and integrity verification.
SHA stands for Secure Hash algorithm and SHA 256 is a part of the SHA
family of algorithms where the hash value will always be 256 bits
irrespective of the size of plain text. This section explains how to get
SHA256 hashed string for a given plaintext.

In ABAP code, you can use the CALCULATE_HASH_FOR_RAW function

module or the class method cl_abap_message_digest=>
calculate_hash_for_char to calculate the hash value for XSTRING typed
data. You can concatenate the plain text into the XSTRING variable in
BYTE MODE. See the following ABAP code for hashing technique.

ABAP Code:
Data: lv_string TYPE string,
lv_hash TYPE string.

cl_abap_message_digest=>calculate_hash_for_char(
EXPORTING
if_algorithm = 'sha256'
if_data = lv_string
IMPORTING
ef_hashstring = lv_hash
).
Or
DATA: lv_xstring TYPE xstring,
lv_hash TYPE hash160.
" Calculate the hash value for the entire buffer/table
CALL FUNCTION 'CALCULATE_HASH_FOR_RAW'
EXPORTING
data = lv_xstring
IMPORTING
hash = lv_hash
EXCEPTIONS
unknown_alg = 1
param_error = 2
internal_error = 3
OTHERS = 4.

AMDP Code:
HANA SQLScript provides a function that returns hashed values for a
given string. See the following sample code.

METHOD test_generate_period BY DATABASE PROCEDURE FOR HDB

LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
ex_out = SELECT HASH_SHA256 (TO_BINARY(:input)) "hash" FROM
DUMMY;
ENDMETHOD.

Similarly, you can use the function HASH_MD5 to return a 32-byte hash
value. The hash value is calculated using an MD5 (Message Digest Method
5) algorithm.

SQL Injection

SQL Injection is one of the security vulnerabilities that allow attackers to

interfere with queries. SQL injection can enable the attacker to access,
modify, or delete sensitive data, or to execute unauthorized commands on
the database. You need to validate and sanitize the user input before using
it SQL statement. To avoid SQL injection attacks, SQLScript provides
multiple SQL injection prevention functions. The functions are:
• ESCAPE_SINGLE_QUOTES – The function returns an input string with
escaped single quotes.
• ESCAPE_DOUBLE_QUOTES - The function returns an input string with
escaped double quotes.
 • IS_SQL_INJECTION_SAFE – The function returns 1 if there is no possible
SQL injection; otherwise returns 0.

Password Generation

SAP ABAP provides multiple function modules to generate a random

password. The function module RSEC_GENERATE_PASSWORD can be
used to generate a password. SAP SQLScript provides a SQL function
GENERATE_PASSWORD to generate the password. You can specify the
length of the password to be generated. The user group name can be
specified in the function. The system administrator can configure a
password policy for a user group. The function uses the password length
and composition attributes of the password policy. The composition
attribute defines the requirement of lowercase, uppercase, numerical digits,
and special characters.

DECLARE lv_password NVARCHAR(16);

SELECT GENERATE_PASSWORD(16) INTO lv_password FROM Dummy;

Random Number

ABAP provides multiple ABAP classes such as CL_ABAP_RANDOM_INT,

CL_ABAP_RANDOM_FLOAT, and CL_ABAP_RANDOM_PACKED to
generate the random numbers. SAP HANA SQLScript offers the function
RAND_SECURE and RAND to return a pseudo-random double value
between 0.0 and 1.0 that is safe for cryptographic or security purposes. The
RAND is to generate pseudo-random numbers. You can also use the SEED
function to initialize the random number generator.

DECLARE lv_random_number INT;

SELECT CAST(RAND(0, 1) * 10 + 1 AS INT) INTO lv_random_number FROM
DUMMY;
--or
SELECT CAST(RAND(0, 1, SEED(42)) * 10 + 1 AS INT) INTO lv_random_number
FROM DUMMY;
Spatial Data
Spatial data represents two-dimension geometries in the forms of points,
line strings, and polygons. The common spatial data operations include
calculating distance and determining the union and intersection of multiple
objects. Using SAP HANA spatial resources, you can turn location data
into business value. Consider the scenario is that given a list of suppliers
with location (latitude and longitude) and identify 5 nearest suppliers for a
given location point. You need a big code to implement this requirement in
the ABAP code. You can use HANA SQLScript spatial concept to provide
the solution.

The solution is provided to get the nearest points for a given input table of
latitude and longitude. The code uses WGS84 (SRID 4326) as a spatial
reference system. The SRID 4326 provides a spheroidal reference surface
for the Earth and is used by the Global positioning system (GPS). The
assumption is that the vendors’ address is associated with the GEOLOC
table. In AMDP, you can handle the same without a permanent table
(GEOLOC) but you need to populate the longitude and latitude for the
vendor and pass it as an internal table. AMDP can use the internal table as
the temporary table. You can use SQL query to do the same.

TYPES:
begin of ty_supp,
lifnr TYPE lfa1-lifnr,
addrnumber type ADRNR,
city type ORT01_GP,
state type REGIO,
latitude type geolat,
longitude type geolon,
distance type f,
end of TY_SUPP.
DATA: lt_nsupplier TYPE TABLE of ty_supp.

lv_latitude = ‘’. “Input value

lv_longitude = ‘’. “Input value
CALL METHOD ZCL_SPATIAL_AMDP=>get_nearest_suppliers(
EXPORTING i_latitude = lv_latitude i_longitude = lv_longitude
lt_supplier = lt_supplier
IMPORTING et_nearest = lt_nsupplier ).
AMDP Code:
METHOD get_nearest_suppliers
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING geoloc, lfa1.

et_nearest = SELECT TOP 5

lifnr, addrnumber, ort01 as city, regio as state, latitude, longitude,
NEW ST_POINT(i_longitude, i_latitude).ST_SRID(4326).ST_Distance(NEW
ST_POINT( g.longitude, g.latitude ), 'mile') AS distance
FROM geoloc g
inner join lfa1 as l on l.adrnr = g.addrnumber
WHERE mandt = SESSION_CONTEXT( ‘CLIENT’ )
ORDER BY distance;

ENDMETHOD.

AMDP code uses the internal table as the reference (instead of the geoloc
table).
METHOD get_nearest_suppliers
BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.

lt_points = select lifnr, addrnumber, city, latitude, longitude,

NEW ST_POINT(i_longitude, i_latitude).ST_SRID(4326) as geo
From :lt_input;
et_nearest = SELECT TOP 5geo.ST_AsGeoJSON() as geojson,
NEW ST_POINT(i_longitude,
i_latitude).ST_SRID(4326).ST_Distance(geo, 'mile') AS distance
FROM :lt_points
ORDER BY distance;

ENDMETHOD.

SAP provides a transparent table GEOLOC where you can enter latitude,
and longitude values for the address number. SAP provided the ABAP
class CL_GEOLOC to fetch latitude and longitude. You can use the
program UPLOAD_ZIP5GOLD to load geo-related information.

ABAP Code
The following ABAP code snippet to fetch the list of the suppliers for the
given latitude, longitude, and distance (in miles).
form get_suppliers_within_miles USING iv_miles type ICL_VAL_DISTANCE
iv_latitude TYPE GEOLAT iv_longitude TYPE GEOLON TABLES et_supplier
TYPE TY_SUPPLIER.

data:
lv_ngeo TYPE geolon,
lv_sgeo TYPE geolon,
lv_egeo TYPE geolat,
lv_wgeo TYPE geolat.

CALL FUNCTION 'ICL_SUPPL_NEXTGEOLOCATION'

EXPORTING
iv_latitude = iv_latitude
iv_longitude = iv_longitude
iv_distance = iv_miles
IMPORTING
ev_ngeo = lv_ngeo
ev_sgeo = lv_sgeo
ev_egeo = lv_wgeo
ev_wgeo = lv_egeo.

SELECT l~lifnr, g~addrnumber, g~latitude as latitude, g~longitude as longitude,

l~ORT01 as city, l~regio as state, cast( 0 as dec ) as distance
INTO TABLE @DATA(et_supplier)
FROM geoloc as g
INNER JOIN LFA1 as l
on l~ADRNR = g~addrnumber
WHERE
( g~longitude BETWEEN @lv_ngeo AND @lv_sgeo " within distance
- longitude
AND g~latitude BETWEEN @lv_egeo AND @lv_wgeo ) OR " within
distance - latitude
( g~longitude BETWEEN @lv_sgeo AND @lv_ngeo
AND g~latitude BETWEEN @lv_wgeo AND @lv_egeo ).

LOOP AT et_supplier ASSIGNING FIELD-SYMBOL(<fs_supplier>).

CALL METHOD CL_GEOCALC=>distance_by_lonlat(
EXPORTING latitude1 = iv_latitude longitude1 = iv_longitude
latitude2 = <fs_supplier>-latitude longitude2 = <fs_supplier>-longitude
IMPORTING DISTANCE = lv_distance ).
<fs_supplier>-distance = lv_distance.
ENDLOOP.
sort et_supplier by distance.

endform.
In HANA SQLScript, spatial data types are defined as object-oriented types.
You need to instantiate the data type using the clause NEW. Each data
types have several methods and you can access the methods from
instantiated data type. Most of the methods (such as ST_DISTANCE,
ST_INTERSECTS) are defined at ST_GEOMETRY The list of a few spatial
data types is listed in the table.

Data Type Description

ST_POINT Point spatial data type
ST_POLYGON Area of space using an exterior ring and could contain
zero to n interior rings.
ST_LINESTRING Multi-segment line using straight line segments between
control points
ST_CIRCULARSTRING Circular line segments between control points
ST_CURVE Supertype for types representing lines using a sequence
of points.
ST_GEOMETRY Maximal supertype of the geometry type hierarchy.

Spatial clustering is used to group a set of points to certain criteria into

clustering. SAP PAL (Predictive Analysis Library) provides the clustering
algorithms with numerical data values. SAP HANA spatial function
supports multiple clustering algorithms based on the spatial data type
point. The supported algorithms include Grid, Hexagon, K-Means, and
DBSCAN clustering algorithms. Each algorithm is associated with its
parameters. Clustering provides many insights into the data set including:
• Cluster Id – Association of each data point to a cluster
• Cluster characteristics such as Centroid, Envelope
• Aggregate functions

SQLScript Spatial clause CLUSTER BY can be used in the aggregate GROUP

BY and window function OVER and PARTITION clauses. See the
following code snippets for clustering the households based on their
location.

lt_kmean_clust =
SELECT ST_ClusterId() AS cluster_id,
ST_ClusterCentroid() AS centroid,
COUNT(*) AS number_of_households,
AVG(INCOME) AS average_cluster_income
FROM ZT_HOUSEHOLDS
GROUP CLUSTER BY LOCATION USING KMEANS CLUSTERS 100;
Note that you may get the SQL error code 7 when the spatial feature is not
supported by your version. For more references, check the SAP help
https://tinyurl.com/4wd486vj.
The Author

Selva Lakshmanan has a Doctorate in Computer Science.

He has been an SAP practitioner for over 20 years with
experience in Business process architecture, Application
Development, Support, and Optimization with various
large public and private sector companies. He has an in-
depth knowledge of SAP modules FI/CO, MM, SD, SRM,
SCM, and production planning. He has worked on cross-
industry verticals and is very experienced in providing solutions for bridging
the gap between SAP and customer requirements. He has published a few
SAP books.