SAP HANA SQL Script – First Steps

Hello all to the very first SAP HANA SQL Script specific tutorial post. In this
post, we talk about what SQL is, how it is relevant in HANA development and
if it is as hard as it appears to be.

Over the years, while working on different developments in SAP, the one
constant I always found funny was that whenever I had a screen open with
some random lines of code open, anyone who saw me looked at me as a
genius. Coding is a fear that many SAP back end developers carry around
without ever facing it. Through my series of tutorials, I intend to present SQL
of HANA in a simple and fun way to my audience so that you can finally see
how handicapped you currently are how powerful you can become with the
knowledge of SQL. Trust me with your time and patience and I assure you of
excellence.

What is SAP HANA SQL Script now?

To anyone new to technology, let’s talk about what SQL really is. SQL stands
for Structured Query Language and is used to communicate with the database.
It’s a language that relational databases understand. So if you want to ask a
school’s database to provide you the list of students from the sixth grade with
attendance less than 50%, all you need is a simple SQL request to get the
data. Such requests are called “Queries”. Similarly, there are different type of
commands for different purposes – to add data to the tables in database, to
change data, renaming tables and many more.

With my enterprise HANA tutorials, you will see that you don’t need to code
to create most views and tables in HANA. You have a simple graphical interface
to drag and drop and create the logic you need. But there are some times
where it’s more flexible to create an HANA object with SQL and I personally
find it more fun to do so. Plus sometimes, performance issues cause
developers to switch to SQL also as it gives you the ultimate power to run a
SQL query in the best way you feel fit. Whenever you use a standard graphical
logic without coding, you allow the software to decide the best path of
execution (which is never a bad thing as usually it knows the optimal way)
but if softwares were perfect, you and I would be looking for other lines of
work.
Bottom-line is that SQL makes gives you the raw power to do amazing things
and by choosing to be afraid, you let go of an opportunity to excel at your
craft.

Can you pick up HANA SQL with no coding experience?

I started learning SQL from scratch starting with basic statements working all
the way up to complex queries and that will be how I structure this tutorial –
just the way I picked it up. Again, with ample amount of time, practice and
regularly reading these tutorials, I assure you that you will be a great SQL
coder in no time.

Goodbye for now future SQL Experts! SAP H AN A SQL Script

SAP HANA SQL Data types – Explained with
an example
One of the primary concepts of HANA SQL or any other programming language
is the data type. To understand what HANA SQL data types mean, let’s take
some examples.

The fruit shop back story

Let’s say you and I work at a fruit shop. I am employee number 1001 and you
are employee 1002. We record some sales data for 2 days of our hard work.

Employee
Product Date Quantity Sold(kg)
Nr.

1001 Apples 1/1/2016 10

1001 Oranges 1/1/2016 5

1002 Apples 1/2/2016 20

1002 Oranges 1/2/2016 10

There are two very important things that we can infer from this data:

1. You are a better salesman that I am

2. Data collected can be of different types. Let’s discuss the different columns
that we have here

Product column only has characters from A-Z and no numbers.

Date column has only dates

Employee Nr. and Quantity Sold(kg) have only numbers and no characters

Data types in database terms is a literal translation of itself. The type of data
it represents is a data type. By specifying the data type, you tell the database
what kind of a value it can expect in that particular field. There a list of SAP
HANA data types available which you can refer on this link (SAP
documentation links do change frequently so let me know in the comments if
it stops working in the future).

HANA SQL Data types – Only the really important ones !!!
More importantly, let me list out the major HANA SQL data types you need to
know to work on a real project. These will be enough for most of the scenarios
you face. For everything else, there’s that link above.

Data type Primary Purpose Example

Used to represent Date values. Default format is YYYY-

DATE 2011-11-20
MM-DD which can be changed as per requirement

Used to represent time. Default format is HH24:MI:SS

TIME which stands for Hours in 24hour 14:20:56
format:Minutes:Seconds

Used to represent whole numbers within the range -

INTEGER 25
2,147,483,648 to 2,147,483,647

DECIMAL Used to represent numbers with fixed point decimals 25.344689

Used to store a character strings 1 to 5000 characters

NVARCHAR abcxyz3h4
long

Note: NVARCHAR is always the preferred datatype over VARCHAR because

it supports unicode character data

So every time, you create a field or a variable (=an object that holds a single
data point), you need to tell HANA what kind of data to expect.

Take a look back at our first table on fruit sales and take a guess on what data
types you think they might be based on the above information.

I will place space in between to push the answers away . Scroll down for the
answers after you have your guess ready.
HANA SQL Data types Knowledge Check: Time for the
answers
1. Product column only has characters from A-Z and no numbers.

It has to be a NVARCHAR as we need to store alphabets.

2. Date column has only dates

Quite obviously it has to be the DATE data type

3. Employee Nr. and Quantity Sold(kg) have only numbers and no characters

Now here comes the tricky part. Let’s start with Quantity Sold(kg). It is a
number so it can be held by INTEGER, DECIMAL and NVARCHAR (as you can
also store numbers as characters). This field contains the number of a
particular fruit that you sold which will always be a whole number like 1, 2 or
4 and never like 1.5 or 1.34 (in which case you probably took a bite of the
fruit before you tried to sell it to a poor customer). So now we have 2 options
– INTEGER and NVARCHAR. Both of them CAN store values for this field but
which one SHOULD?

Now ask yourself, will at some point will someone try to add, subtract or do
arithmetic calculations on this field. For example, the store manager may try
to find the total number of apples sold on a particular day by adding up the
individual employee’s sales volume for that day. In our example, the total
number of apples sold on day 1 was (10+20 = 30). You can only do these
calculations with a numeric data type which is either INTEGER or DECIMAL.
Since we have already ruled out DECIMAL, we can infer that INTEGER data
type would be the correct option here.

Coming back to the Employee Nr. now, we always have whole numbers as
employee IDs so we can safely rule out DECIMAL. Now, we ask the question
again will someone want to do any math on this field. Logic dictates that It
makes no sense to add or subtract 2 employee IDs and hence we declare it
as a NVARCHAR data type so that even if some crazy dude tries to do some
math on this field someday, HANA throws an error showing him his logical
fallacy.

I hope this was easy to understand and follow. Stay tuned for my further
HANA SQL Tutorials for SAP HANA.
SQL Join Types in SAP
Hello one and all. Welcome to the next edition of our SAP HANA Tutorial
series. This is another one of our checkpoint courses where we understand
one of the basic concepts of database design – SQL Join Types in SAP. Since
this is a common tutorial, all of these types may not be applicable to ABAP,
BW and Enterprise HANA. Each of them supports some of these which we will
understand in each of those courses individually. But for now, this tutorial is
to understand what joins mean.

Most of the times, one table does not contain all the data needed for analyzing
the problem. You might have to look into other tables to find the other fields
that you need. The primary way to achieve this is via joins. But there are
different types of joins. Let’s look at each of them with an example.

Fruits make everything simple..even SQL Join

Types
Five students in a class are asked to input their favorite fruit and vegetable
into two database tables. They are free to not enter any fruit or vegetable if
they don’t like any.

Table 1: The Fruit table

Student Fruit

Shyam Mango

John Banana

David Orange

Maria Apple

Table 2: The Vege-Table

Student Vegetable
 Shyam Potato

David Carrot

Maria Peas

Lia Radish

After the data entry is complete, we can see that Lia doesn’t like any fruit and
John doesn’t like any vegetable apparently.

SAP SQL Join Types #1 : Inner Join

Now, let’s say our first analytical requirement is to get a singular view of all
the students with fields (Student, Fruit, Vegetable) who like at least one fruit
and at least one vegetable. This is a classic example of an INNER JOIN.

Student Fruit Vegetable

Shyam Mango Potato

David Orange Carrot

Maria Apple Peas

An INNER JOIN returns a view with result set that is common between all
involved tables.

SAP SQL Join Types #2: Left Outer Join

The next requirement we have is to get a singular view of all the students with
fields (Student, Fruit, Vegetable) who like at least one fruit even if they don’t
like any vegetables. This type of join is a LEFT OUTER JOIN.

Student Fruit Vegetables

Shyam Mango Potato

 John Banana ?

David Orange Carrot

Maria Apple Peas

A LEFT OUTER JOIN returns all the entries in the left table but only matching
entries in the right

Note: The ‘?’ in the data is a NULL entry. As discussed earlier, NULL denotes
the existence of nothing. NULL is not blank or zero. It’s just nothing. Since
John likes no vegetables, a null value is placed there. In SAP BW and ABAP
output, NULL maybe represented by ‘#’ whereas in Enterprise HANA, ‘?’ is
the default display of NULL values.

SAP SQL Join Types #3: Right Outer Join

Next, we now require a singular view of all the students with fields (Student,
Fruit, Vegetable) who like at least one vegetable even if they don’t like any
fruits. This is exactly the converse of our previous requirement. This type of
join is a RIGHT OUTER JOIN.

Student Vegetables Fruit

Shyam Potato Mango

David Carrot Orange

Maria Peas Apple

Lia Radish ?

A RIGHT OUTER JOIN returns all the entries in the right table but only
matching entries in the left

Note: This is kind of a redundant type of join as the position of these tables
can be reversed and a LEFT OUTER JOIN can be applied to achieve the same
results. For this same reason, RIGHT OUTER JOIN is rarely used and is also
considered a bad practice in terms of performance of SAP HANA views. So try
to avoid using it.

Student Fruit Vegetables

Shyam Mango Potato

John Banana ?

David Orange Carrot

Maria Apple Peas

Lia ? Radish

SAP SQL Join Types #4: Full Outer Join

Next, we require the data of all the students with fields (Student, Fruit,
Vegetable) who like at least one fruit or at least one vegetable. This is a classic
example of an FULL OUTER JOIN.

Student Fruit Vegetables

Shyam Mango Potato

John Banana ?

David Orange Carrot

Maria Apple Peas

Lia ? Radish

A FULL OUTER JOIN returns all the data from all involved tables regardless
of whether they have matching values or not on the join condition.
This one is rarely used as we never usually need all the key values from both
tables. As seen from the example, this results in a lot of nulls. Nevertheless,
it is a rare need.

The main type of JOINS actually used in real time scenarios are INNER JOINS
and LEFT OUTER JOINS. LEFT OUTER JOINS are the preferred join type in
terms of performance as they require only scanning of one table to complete
the join.

SAP HANA SQL Script Specific Join Types

There are two other join types available in enterprise HANA called
REFERENTIAL JOIN and TEXT JOIN. A REFERENTIAL JOIN is the same
as an INNER JOIN but better in performance but should only be selected
when it is sure that the referential integrity is maintained which in regular
human English translates to mean that all values for the join condition fields in
the left table exist in the right table for their corresponding fields as well. This
is very difficult to guarantee and hence due to this involved risk, a
REFERENTIAL JOIN is best left untouched.

A TEXT JOIN is used for tables containing descriptions of fields. The text
tables/descriptive tables are always kept on the right hand side and their
language column needs to be specified.

A TEXT JOIN usually looks like the below.

Key Table:

Vegetable
Student
code

Shyam PO

David CA

Text Table:

Vegetable
Description Language
Code
 PO Potato EN

PO Kartoffel DE

CA Carrot EN

CA Karotte DE

which join to become the below table with descriptions of the same vegetable
in two different languages – English and German

Vegetable
Student Description Language
Code

Shyam PO Potato EN

Shyam PO Kartoffel DE

David CA Carrot EN

David CA Karotte DE

Note: As you might have already noticed, I have referred to the resultant
output of joining tables as “Views”. Views is the actual technical term for this
result set. A view is just a virtual output of a join and is not persisted/stored
on the disk like a table. The result set of a view only exists during run-time.

Thank you for reading this tutorial on joins and if you liked it, please show
your support by sharing this document across social media by pressing the
share buttons below and also don’t forget to subscribe to our newsletter for
alerts on new tutorials that are added regularly.

Happy Learning!
SQL UNION and SQL UNION ALL in
SAP
Welcome again to this new tutorial on SQL UNION in SAP where we discuss
the UNION of data sets and try to understand the difference between UNION
and UNION ALL. Unions happen in SAP BW as well as enterprise HANA
graphical views and in SQL Script UNION statements. This one would be a
short and simple tutorial with an example as always.

The SQL UNION in SAP

Consider a data set for North America division of a company

Product

Shoes

Bags

Gloves

Now assume that we have another data set for the South American division
of the same company

Product

Shoes

Caps

To get the entire product portfolio of the company in the American region, the
operation required between these two data sets would be a UNION. This is
because a UNION vertically combines data sets like piling one stack of
potatoes. The result would be as shown in the below table.

Product

Shoes

Bags

Gloves

Caps

Quite simple, isn’t it? But there is another type of union operator that we can
use.

The SQL UNION ALL in SAP

For the same set of tables, if we do a UNION ALL, the result would be:

Product

Shoes

Bags

Gloves

Shoes

Caps

Do you notice the difference here? Take a good hard look.

Difference between UNION and UNION ALL

You might have noticed this by now that UNION ALL did not remove the
duplicates and ‘Shoes’ was repeated twice in the data set which in this case
wasn’t a desirable result.

But why would one still use something like a UNION ALL? Excellent question.

In cases where there is absolutely no doubt that the merging data sets have
distinct values, it’s better to use a UNION ALL so that there is no time wasted
by the system in sorting and deleting duplicates from this final data set. For
performance reasons, UNION ALL is a true gem of an option and should be
used wherever possible.

Creating an SAP HANA table with SQL

Script
Welcome to the next SQL script for SAP HANA tutorial for beginners. In this
tutorial, we will create a SAP HANA table with SQL script. This is a continuation
of our series outlining the different ways in which you can create tables in SAP
HANA. It is highly recommended that you read the first part where we discuss
the graphical method.

To recap, the main methods to create a table are:

1. Using the graphical method that has been already discussed.

2. Using SQL Script which will be discussed here.
3. Using the HDB tables method discussed in a separate tutorial.
4. Using the HDBDD method discussed in a separate
tutorial.(Recommended method in non XSA projects).
5. Using the HDBCDS method which would be discussed in a future
tutorial(Recommended method in XSA projects).
SAP HANA Table: HANA Create statement Syntax
Let’s create the exact same table that we did using the graphical method with
fields Customer number, First name, Last Name and revenue in USD. The
syntax to be used is

CREATE <table _type> “ <table _name>”

“<field1>” <data type 1> <Nullability_criteria>

“<field2>” <data type 2> <Nullability_criteria>

“<fieldN>” <data type N> <Nullability_criteria>

PRIMARY KEY (“<primary_key_field1>”,”<primary_key_field2>” )

);

What does this even mean?

<table_type> can have many values but the important ones are ROW and
COLUMN. I have discussed what these are in a separate tutorial. The default
is a ROW table and hence even if do not write ROW, a row table will get
created. But for analytical applications, we prefer to use COLUMN tables and
hence here, <table_type> should have COLUMN in all analytical requirements.

<table_name> is a self-explanatory statement wherein you specify the table

name which you wish to create. The important point to note here is that the
schema name where the table needs to be created needs to be specified in
this statement. For example if you need to create the table ORANGES under
schema FRUITS, then <table_name> would be “FRUITS”.”ORANGES”

<field> refers to the name of the fields you wish to add in this table.

<data type> refers to the data type of the field you are trying to add.
<Nullability_criteria> takes the values NOT NULL if the field is a primary
key or if you want to restrict the incoming data in this table to have no NULL
values.

<primary_key_field> takes all the primary key field names separated by a

comma.

Note: Always be careful with the position of the brackets. Try to follow the
syntax shown above. Also end all SQL statements with a semicolon.;

SQL Script example to create a SAP HANA table

With this logic, our SQL code for a new table (which we cleverly name as
CUST_REV_SQL) with the same structure as the one we built in the graphical
table tutorial would look as below.

CREATE COLUMN TABLE “0TEACHMEHANA”.”CUST_REV_SQL”

(“CUST_ID” NVARCHAR(10) NOT NULL ,

“FIRST_NAME” NVARCHAR(20),

“LAST_NAME” NVARCHAR(20),

“REVENUE_USD” INTEGER ,

PRIMARY KEY (“CUST_ID”))

Open the SQL editor by clicking somewhere in the schema tree and then
pressing the SQL button as it becomes active.
Let’s copy this code to the SQL editor and press the execute button marked
below or press the F8 button after selecting the code.
The message for successful execution is shown below. Now we refresh the
Tables folder to see this newly created masterpiece.

The new table has been created as seen and double clicking on it reveals that
the structure is also as we intended it to be.
This concludes this tutorial on creating tables with SQL script in SAP HANA.
Please read the next part of Table creation tutorial – Creating HDB tables. You
will really find it fascinating.
Creating an SAP HANA HDB Table
Welcome to the next tutorial on the three part series explain different ways to
create tables. In this one, we learn how to create an SAP HANA HDB table. It
is recommended that you also read the first two parts as well.

To recap, the main methods to create a table are:

1. Using the graphical method that has been already discussed.

2. Using the SQL script method which has been already discussed.
3. Using the HDB tables method which will be discussed here.
4. Using the HDBDD method discussed in a separate
tutorial.(Recommended method in non XSA projects).
5. Using the HDBCDS method which would be discussed in a future
tutorial(Recommended method in XSA projects).

To start off, if you remember my earlier tutorials, I recommended to always

remain in the “SAP HANA Development” perspective of HANA. For this tutorial,
it is mandatory that you are in the development perspective to move forward.
Read this tutorial again if you don’t remember how to switch perspectives.

Grouping Development objects – Creating a SAP HANA

Package
Until now, we have done everything in the Catalog folder, but the HDB table
code is to be written in the Content folder. All such objects and future
analytical objects that we create will be in the Content folder. All developments
have to be grouped under a “Package”. Package is just another layer of
grouping for all development and modeling objects. Inside content, let’s create
a new package for all our future developments. To do this, right click on the
content folder, and follow the path shown below to click on “Package”
Give it a name and description and press OK. I am prefixing a zero to make
sure my package comes up on top as it is sorted by symbols, numbers and
then alphabets. You can provide any relevant names.

Then move to the repositories tab as shown below. If you are going here for
the first time, you need to import the Workspace into your local memory.
To do this, press “Import Remote Workspace” after right clicking on the
(Default) repository as shown below.

Provide a folder for this to be imported to.

Once done, you will notice a small tick symbol on top of the repository icon as
marked below.

Creating an SAP HANA HDB table in the repository

Now, right click on the newly created package and select the “Other” from the
context menu path shown the illustration.
Note: If you don’t see the repository tab and are lost at this point, that means
you are not in the Development perspective. You can see this on the top right
of your HANA Studio as shown below.

A wizard opens up. Write ‘table’ in the search bar marked by the red arrow as
shown below. The objects with Table in the name show up. Select Database
table as circled below and click next.
Provide a table name as shown below and leave the template as blank.
This will open up a blank editor screen where in you need to put in the HDB
Table code. This is a simple code although it is not SQL It is a HANA internal
syntax.

The syntax to be used is:

table.schemaName = “<Schema_Name>” ;

table.tableType = <Type_of_table> ;

table.columns =

[
{name = “<field1>” ; sqlType = <SQL_Datatype1>; length =
<Length_of_characters>;comment = “<Optional_Description>”;},

{name = “<field2>” ; sqlType = <SQL_Datatype2>; length =

<Length_of_characters>;comment = “<Optional_Description>”;},

{name = “<fieldN>” ; sqlType = <SQL_DatatypeN>; length =

<Length_of_characters>;comment = “<Optional_Description>”;},

];

table.primaryKey.pkcolumns =
[“<primary_key_field1>”,”<primary_key_field2>”];

What does this even mean?

<Schema_Name> refers to the target schema where you wish the table to
be created in.

<Type_of_table> can take two values ROWSTORE and COLUMNSTORE. You

should ideally keep this as COLUMNSTORE unless there is a special
requirement to do so. To know the difference between ROWSTORE and
COLUMNSTORE click here.

<field> refers to the name of the fields you wish to add in this table.

<SQL_Datatype> refers to the datatype of the field

length = <Length_of_characters>; This section signifies the length of your

field. Please omit this for INTEGER types as they don’t require a specified
length.

comment = “<Optional_Description>”; This is also an optional section

specifying the description of a field.

<primary_key_field> takes all the primary key field names separated by a

comma.

Let’s create the same table structure we created in the other cases. To
download the code I used below, click here.
According to the above syntax, the code would be as below. Press the activate
button marked by the arrow below.

This should create this table in the 0TEACHMEHANA schema. Once built, it’s
the same as a regular table. Go back to the Systems tab and in this schema,
go to the tables folder and refresh it.
You would notice that the table has been created. It works the same way but
has some additional capabilities that we will discuss in upcoming tutorials.
Also, you might notice that the package name is also prefixed to the table
name automatically. That’s something exclusive to HDB Tables.
Double clicking on the HDB table shows the structure has been defined as
required.

This marks the successful completion of our tutorial.

HDBTables are the recommended form of tables to be used whenever

custom tables are to be created. The only reason I use the other forms
of table creation are for quickly creating and testing something but
for actual deliverables, only HDB tables must be created.

Edit (25-Jun-2017): Only CDS based tables are the best practice as of this date. These can be
HDBDD tables (described in the next tutorial) or HDBCDS table (for XSA based projects)
Their advantages will be further clear in our further tutorials so stay tuned for
new posts.

Thank you for reading this tutorial on creating an SAP HANA HDB table and if
you liked it, please share this document across social media using the share
buttons below. Also, don’t forget to subscribe to our newsletter to get the
latest updates on added tutorials.

Creating HDBDD Table using CDS method in

SAP HANA
Hi All. Welcome to a new tutorial on creating a CDS based HDBDD Table in
SAP HANA. In this one, we learn to create a table using the SAP HANA CDS
(Core Data Service) method. The two recommended methods today to create
a custom table in SAP HANA are using the HDBDD file or to use the HDBCDS
file (when you have an XS Advanced or XSA installation).

The method to create a table elaborated on this website are:

1. Using the graphical method discussed in a separate tutorial.

2. Using the SQL Script method discussed in a separate tutorial.
3. Using the HDB tables method discussed in a separate tutorial.
4. Using the HDBDD method discussed here(Recommended method in non
XSA projects).
5. Using the HDBCDS method which would be discussed in a future
tutorial(Recommended method in XSA projects).

For anyone in a hurry, here’s the video version of this tutorial on YouTube. If
you prefer a written one, please read on. Also, please subscribe to our
YouTube channel to get video tutorials weeks before the written ones.

CDS HDBDD table : Using Web Development

Workbench
We are using the web development workbench to create this HDBDD file. You
can also do this in the SAP HANA studio under the repository tab.

Once you open the SAP HANA web development workbench, click on the
Catalog to open the Catalog section.

The catalog link opens up showing the packages you are authorized to view.
Right click on the package where you wish to develop this code.

From the context menu, choose New-> File

A window opens up asking for the file name

We provide a file name TABLES with the extension hdbdd.

The editor opens up.

Now, we paste the below code into this editor to create our table.

namespace TEACHMEHANA;

@Schema: 'SHYAM'

context TABLES {

Entity CUST_REV_CDS {

CUST_ID : String (10);

FIRST_NAME : String (20);

LAST_NAME : String(20);

REVENUE_USD : Integer;
};

};

The namespace needs to define the package under which this file is created.

@Schema defines the schema under which the created table(s) would reside
under.

The main context defines the file name that was given at the time of
HDBDD creation.

A table is a persistent entity in SAP HANA CDS and hence the below
statement declares a table (entity) CUST_REV_CDS.

Entity CUST_REV_CDS : The next part declares the columns in this table. Notice
that the data types are different than in regular SQL. There is no NVARCHAR.
Instead the declaration uses a String datatype. This is because CDS has
slightly different datatypes.

CUST_ID : String (10);

FIRST_NAME : String (20);

LAST_NAME : String(20);

REVENUE_USD : Integer;

};

SAP has an online page dedicated to the datatype mappings which you can
refer to in this regard. Click here to reach that page. A screenshot of that page
currently is as below.

SAP HANA to CDS Data-Type Mapping

Mapping table for SAP HANA (hdbtable) and Core Data Services (CDS) types.

Although CDS defines its own system of data types, the list of types is roughly equivalent to the
data types available in SAP HANA (hdbtable); the difference between CDS data types and SAP
HANA data types is mostly in the type names. The following table lists the SAP HANA
(hdbtable) data types and indicates what the equivalent type is in CDS.

Table 17: Mapping SAP HANA and CDS Types

SAP HANA Type (hdbtable) CDS Type (hdbdd)

NVARCHAR String

SHORTTEXT String

NCLOB LargeString

TEXT LargeString

VARBINARY Binary

BLOB LargeBinary

INTEGER Integer

INT Integer

BIGINT Integer64

DECIMAL(p,s) Decimal(p,s)

DECIMAL DecimalFloat

DOUBLE BinaryFloat

DAYDATE LocalDate

DATE LocalDate

SECONDTIME LocalTime

TIME LocalTime

SECONDDATE UTCDateTime

LONGDATE UTCTimestamp

TIMESTAMP UTCTimestamp

ALPHANUM hana.ALPHANUM
 Table 17: Mapping SAP HANA and CDS Types

SAP HANA Type (hdbtable) CDS Type (hdbdd)

SMALLINT hana.SMALLINT

TINYINT hana.TINYINT

SMALLDECIMAL hana.SMALLDECIMAL

REAL hana.REAL

VARCHAR hana.VARCHAR

CLOB hana.CLOB

BINARY hana.BINARY

ST_POINT hana.ST_POINT

ST_GEOMETRY hana.ST_GEOMETRY
Once done, right click on the file and click “Activate”.
The cross symbol disappears from the file confirming that it is now active. The
table CUST_REV_CDS should also now be created in the SHYAM schema as
defined.
Now come back to the Web based development workbench. Click on the
Editor.

Now, expand the Catalog, the schema, and the tables folder.

We see that the table has been created successfully.

This table can also be loaded using the hdbti method in the same way as the
hdb tables.
SAP HANA SQL Script INSERT
Welcome to the next tutorial explaining the usage of SAP HANA INSERT
statement to insert new records into a HANA Table. This method can be
applied on any table, no matter how it was created – Graphically ,by SQL
CREATE or HDB Table

To start, we need the SQL editor. Click somewhere in the tree under the
system so that the SQL button marked below becomes enabled. Press the
button to bring up the SQL console.

As always, the blank console opens up.

INSERT Data into HANA Table
The syntax for SAP HANA INSERT statement is as below:

INSERT into VALUES (value1, value2, valueN);

Note: Always remember that refers to the table name prefixed with the
schema name as well as there can be tables of the same name in different
schemas.
Repeat this statement as many times as the number of rows you wish to add.
As seen below, I am adding data to the CUST_REV_SQL table which is in the
0TEACHMEHANA schema. System object names like schema names, table
names and field names should be wrapped in double quotes “ ” whereas as
you can see below, data values that have character data types inserted MUST
always be wrapped in single quotes ‘ ’ whereas number do not require any
quotes at all.

As seen from the log below, there are no errors. This means that the data was
inserted successfully.

Now to check if it worked

To confirm, right click on the table and click on data preview.
The raw data tab below confirms that the data is now present in the table.

And if you are wondering – Yes. It was that easy to do this. SQL for HANA is
fairly simple if you understand what you are doing instead of just trying
random experiments off the web. This concludes our second part of the SAP
HANA customized table data load series. Be sure to check out the other two
tutorials as well which are:
1. Loading flat files to SAP HANA in the Enterprise HANA section
2. Linking a CSV file to a HDBTable using HDBTI Configuration file
(Recommended method) which is our next tutorial as well.

Help this website grow by sharing this document on social media by using
the icons below.

Happy Learning!
Import flat file(CSV) with HDB
Table – Table Import Configuration
Welcome again to the next tutorial where we learn how to link a CSV file to
an HDB table. This is the third and last part of the data load to HANA table
tutorial series. This method only works with HDB Tables. Although this is the
longest method, it is the recommended one. Why? Read on to understand.

Be sure to check out our other tutorials on the other data load methods to
SAP HANA:

1. Via flat file loads to SAP HANA

2. Using SAP HANA SQL Script INSERT statement

Importing CSV data file to SAP HANA

repository
In this approach, the CSV file will reside on your SAP HANA server and not the
local desktop. Open HANA studio and go to the repositories tab. If you don’t
see this tab, you are not in the developer perspective.

Under your package, right click and go to New-> Other from the context
menu.
The below window pops up wherein you should select “File” and click Next.
Enter a file name. I have provided it as customer_revenue.csv. Press Finish
when done.
Depending on your HANA Studio default configuration, either a text editor
opens up or ideally an excel window will be opened. I prefer not to use excels
for CSV files as they tend to corrupt the CSV format sometimes. So instead of
filling in the table data here, we just save it blank.
On pressing save, you get the below message. Press Yes.

Now, exit the Excel and excel throws the below message. Press Don’t save
here.

You would notice a new file can be seen inside our package now. The grey
diamond symbol on the file icon means that it is currently inactive.
Right click on the file and then press Open With -> Text Editor.

Sometimes you would get an error on the right side pane saying “The resource
is out of sync with the file system”
In such cases, right click on the file name and click refresh to fix this problem.

Now in the editor that opens up, we paste the CSV data from our tutorial on
flat file upload, copied by opening it in notepad.
Once done, press the activate button marked below.

Now the CSV file would have become active. Notice that the grey diamond
icon has gone away.

Now we have an HDB table we created in an earlier tutorial by the name

CUST_REV_HDB.
Linking the SAP HANA HDB table with this CSV
file – HDBTI configuration
To do this, stay in the repository tab and right click on your package. Again
select New-> Other as shown below.

Write ‘configuration’ in the search bar as shown below. A list of options will
open up. Click on Table Import Configuration inside the Database
Development folder and press Next.
Provide a name to this configuration file and press Finish.
The editor opens up on the right and a file is created in the package as well.
Notice the grey diamond again.This means that this file is inactive.
The syntax of this hdbti file is as given below

import = [

hdbtable = “<hdbtable_package_path>::<hdbtable_name>”;

file = “<csvfile_package_path>:<csvfilename>”;

header = <header_existence>;

delimField = “<delimiter_sumbol>”;

];

<hdbtable_package_path> refers to the package where your HDBtable was

created. If your package was called Fruits, your package path will be “Fruits.
But if you had a package Orange inside the package Fruits and the HDB Table
was inside this Orange package, the path would be “Fruits”.”Orange”

<hdbtable_name> Refers to the table that we wish to link this file to

<csvfile_package_path> Package path where your csv file was created.

<csvfilename> The filename of your CSV flat file.

<header_existence> Can take value true if you have a header row in your
excel. Otherwise, it’s false.

<delimiter_sumbol> refers to the data separator/delimiter which in our case

was commas. Check your data file carefully to understand whether it is a
comma or a semicolon or anything else. Enter it here.

In our case, the code would look like the below. Once done, press activate.
As seen below, the grey diamond symbol has gone away from the hdbti file.
This confirms that it’s active.

To confirm if the data is now linked, we go back to the systems tab and right
click on our table to do a data preview.
The Raw data tab confirms that it is working.

This concludes our three part tutorial of data loads to custom tables on SAP
HANA. For client delivery always use this method as the CSV file also can be
transported to further systems and it also means that the flat file is always in
your HANA server and not the local desktop.

Core SAP HANA SQL script concepts- SELECT

STATEMENT
Welcome to the next series of tutorials on SAP HANA SQL script which will aim at
making you comfortable with the SQL side of SAP HANA. Most of us are scared of
coding but let me assure you that you can pick it up fairly easily if you go through
these tutorials and the exercises which will follow soon.

What data do we use?

In order to understand the concepts, I have created three tables as shown below in
our 0TEACHMEHANA schema and also filled them up with 50 rows of data, each row
corresponding to a unique Employee ID which is also the key field for all these tables.
You can create these tables graphically, by SQL or HDB method and load data by flat
file load, INSERT statements or CSV links to HDB tables .

Bottom line is that we have 3 tables each with some data. Let’s have a look at each.
Data disclaimer: The below data was generated by random data generation tools and
any information displayed below has no association to reality..well.. except my name
in the first row of course which is due to shameless self promotion.

The first table is EMP_MASTER. This is the major master data table containing
information of Employee ID, Gender, Age, e-mail, Phone number, highest educational
qualification, marital status and the number of children an employee has. This table
has 50 records and the below data preview displays a section of this data.

The second table is EMP_NAMES. This is another Master data table that contains the
first names and last names of the same 50 employees as in the EMP_MASTER table.
The third table is a transaction table containing the country in which the employee
works and the salary he/she earns in USD.
I am attaching the data I used in all these tables if you want to use it to play
around. Download it here.

Introduction to SAP HANA SQL script SELECT statement

Now, let’s get to business and start doing some analytics based on these data sets.
To start, we need to open up the SAP HANA SQL Editor like always using the SQL
button below.
SAP HANA SQL SELECT Statement
In the editor, let’s write our first simple SAP HANA SQL Script code to view all the
data from the EMP_MASTER table. The statement used for this is called SELECT and
is the most important statement for any data analysis because it is used for reading
data from tables. The * symbol after SELECT implies that you need all fields from this
table. System statements, keywords and functions always appear in a reddish
maroon colour as shown below whereas the table names and string values would
usually be in blue. Integer values and arithmetic symbols usually appear black. As
you see below, you should always provide the table name as . and not the alone.
Also, it’s best to terminate the SQL statement with a semicolon ; although you can
skip it if you only have one statement. To execute the below, press the execute button
in the SQL editor or F8 on your keyboard.

The result is shown in the ‘Result’ tab as captured below. Also seen below the data is
a log that explains the number of records retrieved, time required to execute the
statement and more.
As seen below, all 50 records were pulled from EMP_MASTER table with all fields
displayed as expected with this execution.
SELECT TOP Clause

Now what if this is a fairly large table and I just wanted to see some of its data to
understand what it holds. Pulling all of it would unnecessarily delay the output with
a long job. But don’t worry. SQL has the TOP keyword just for this requirement. If
you need only the top 10 values of a data set, write TOP 10 after the SELECT keyword
as shown below. You can change the number as per your needs.

As captured below, only the top 10 records are shown.

Now SELECT * is a bad practice in coding since in real business tables you might have
200+ fields and millions of data records. You might only need a few for your analysis
but you end up pulling all of them with the SELECT *. To solve this problem, always
provide the field names you need after the SELECT keyword and separate them with
commas. In the example below, I pick up only the fields EMP_ID, GENDER and
EDUCATION from the EMP_MASTER table.
Also notice that all field names and table/schema names are enclosed in double
quotes. It is a good practice to do so. I recommend doing the same.

Execute the statement and you will see that we get all the relevant data from
EMP_MASTER for only the fields we requested.
Now what if we wanted to see what are the different values a field is containing in
the table. For example for the Gender field, if I want to look at the values it holds in
EMP_MASTER, I can use the SELECT statement as shown below.
On executing this, you can see the problem. I get 50 rows of data even though I only
have distinct gender values.

SQL DISTINCT
To only get the distinct values of a column in any table, just add the keyword
DISTINCT in front of the field name as shown below.

On execution, you would see that we now only get the two unique values for this
field. This helps in some quick analysis sometimes.
Distinct can also be used to find unique combinations of two or more fields. For
example, in the below statement, we try to find the distinct age-gender values in this
pool of 50 employees.

Upon execution, we can see that there are 19 distinct combinations for these two
fields. As mentioned, you can do this for any number of fields in the same way.
I hope this tutorial was helpful to you. Follow the link below to the next part where
you will learn more details of the SAP HANA SQL script SELECT statement. Help this
website grow by sharing this document on social media by using the icons below. Be
sure to subscribe to our newsletter when the message pops up for latest alerts on
new tutorials.
Core SAP HANA SQL script concepts- SELECT
with SQL WHERE conditions
Welcome to the second installment of this SAP HANA SQL Scripts core
concepts section where we try to understand the SQL WHERE condition and
different ways of using it.

SQL WHERE – Because we only take what we need

Most times when we do analysis, we never need all of the data from a table.
We usually need a particular sub-set of this data and when we pick up only
the part we need, this saves a lot of time in query execution and speeds up
performance. These filters are applied using the SQL WHERE clause. After
your table name in the SELECT statement, add WHERE clause to the end
with the condition(s) for the filter. For example, below we pick up all the age
and gender values for all employees whose age is greater than 25 years.
This means that this data would be returned for ages 26 or more.

Also note that the number 25 is written without any double or single quotes.
This is because in EMP_MASTER, I declared it’s datatype as INTEGER and not
as one of the character datatypes.

On pressing execute you can see that 28 rows have been retrieved and on
skimming the data, you realize that the filter was successfully applied.
If you wanted to include employees with age 25, just add an equals sign in
front of the greater than sign to make it a greater than or equal to condition
as shown below.

On execution, you get the data for ages 25 or more as seen below. One of
the 25-year-old employee’s records have been highlighted for reference.
AND / OR Operator

Now there might be multiple filters required rather than one. For example, if
we extend the above case by saying that I now need the list of Age and
Genders from EMP_MASTER for all male employees who are 25 or more in
age. To do this, in the SQL WHERE condition, just add an AND keyword in
front of the first condition and specify the next one. You can keep adding AND
conditions and continue adding more filters like this. I now add the gender
filter as male.

Notice that the value has been enclosed in single quotes. This is really
important. Whenever the value is a string of characters, it should be in single
quotes. Always remember that fields, and object names are always in double
quotes but data values that are characters will always be in single quotes.
On executing this. We see no data. So this can either mean that there is no
row in this table that matches this condition or that we messed up the filter
somehow. In our case, it’s the latter. I screwed this up to teach you an
important lesson – Always check the data before applying filters. If you see
the data from this table in one of the initial screenshots, you will realize that
the filter we need is ‘Male’ and not ’male’. Anything inside a quote is case
sensitive.

Now we correct the filter and capitalise the M in male.

Executing it this time provides the correct results.

The below example shows addition of a third filter.

Upon execution you get the result as below.

Now let’s say we need a result where the output should show employees who
are 25 or above in age and male with no children or people of age 19. This
statement can be a bit misleading and not understood in the way it was meant
to. Read it once again. What it means is we need the data for people who are
19 without any further filters or anyone greater than or equal to 25 years but
this time with the filter of being male and without children.

In this case, we add an OR condition at the end of the previous SQL WHERE
conditions.

On execution, we have the below result. As seen here, due to the OR condition,
we now get even records for Female gender who are 19 years of age where
even one of them has kids. Similarly, there are now males of 19 in the data
with kids. But on everyone at and over 25, the further filters got applied
correctly.
Now what if we wished for the gender and children filters to be applied on the
19 year old as well? Well it’s quite simple. In basic mathematics, we learnt
that brackets are always evaluated first. The same rule applies here. We put
the age restrictions inside a bracket with an OR condition so that this becomes
a single block and then the AND conditions with gender and children filter
make sure we get the correct result.

Upon execution, we see that the result is what we expected.

Lets have a look on the list of operators which can be used with SQL WHERE
clause :

Operator Description

= Equal

<> Not Equal

> Greater than

< Less than

 >= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

Returns multiple values from a

IN
column

SAP HANA SQL SCRIPT LIKE Operator

Now there are situations where we need to return the list of data where a
record starts with/ends with/contains a particular character or set of
characters. This means that whenever you are looking for some sort of a
pattern in the data, you should use like.

Wildcard Characters : % and _

SQL wildcards must be used with SQL LIKE operator. % can be filled with any
number of characters. For example, using LIKE ‘%am’ means that it will return
all data where the string ends with am. The data output may be ‘shyam’
‘xysfasdaam’ or just ‘am’

_ has to be filled with exactly one character. For example using LIKE ‘_hyam’
can return data where string ends with hyam. So the output can be shyam ,
xhyam, yhyam.

These can be used at start of the string, end of it and in between as well. You
can use it as LIKE ‘s__%’.This one tells the SAP HANA system that the string
we are looking for should be at least of length 3 and should start with a
lowercase s. Also can have more characters at the end due to the % wild card
So, the result can be shyam, s12, s56testchar and so on.

So using this knowledge, let’s try to find all records from EMP_MASTER where
the employee’s phone number starts with the number 6. Since the length of
this phone number is irrelevant, we can use a % wildcard as shown below.

The execution result is as below.

Now let’s say our requirement says that the phone numbers we need to look
for are exactly 11 characters in length and start with 2 whilst ending with 8.
In this case of fixed length, we would need to write the query as below using
9 underscores between 2 and 8.
On execution, we see the records as expected in the output.

SAP HANA SQL Script NOT condition

The NOT is an interesting keyword. You can put it in front of any keyword in
the WHERE condition and make it behave opposite to its regular behavior. We
apply the NOT as shown below to the previous statement.

As seen below, the two records we got earlier are now excluded and we now
have 48 records where the phone number doesn’t start with 2 and doesn’t end
with 8.
In the next tutorials, we will learn about new calculated fields and aliases in
SAP HANA SQL script. Be sure to check it out.
Core SAP HANA SQL script
concepts- Calculated fields and Alias
Welcome to the third instalment of this SAP HANA SQL Scripts core concepts
section where we try to understand how to create new calculated fields and
how to rename field names using SQL alias concept.

Now let’s play with transaction data for a while. Let’s see how data in our
EMP_SALARY table looks like.

As seen below, we have 50 records – one for each employee ID showing their
salary as an integer value.
Let’s do an on the fly calculation of a new value – bonus which is 20% of the
base salary. To do this, just add a bracket (optional in this case but good for
clarity) and put in the formula as salary multiplied by 0.2

As you see below, we got the correct results. But the field name is also
displayed as the formula we used for calculating the bonus. This should rather
have a more meaningful name ideally.
SQL ALIAS for fields
To solve the above mentioned problem, we use something known as Alias. An
alias is an alternate name given to something in SQL script. Just add the AS
keyword after the formula and provide the alias field name in double quotes.
In our example, we use BONUS as the new field name.

Execute this script and you would see that the new field name has now
appeared instead of the formula which is much better in terms of presentation.

You can even create new fields with constant values and give them a
meaningful alias.
For example, I create a new field called OTHER_BENEFITS with a constant
value of 10 for each employee.

On execution, you can see that the alias and the values now appear. In this
way, you can create new fields that are not present in the base table.
SQL alias for table names

We already learnt about field aliases where we rename individual fields or

formulas as some other name that we desire. Similarly, we can create table
aliases where we give the table name some other (usually shorter) name so
that it’s easier to type it in when used again and again in the same statement.
This also increases code readability.

For example, to select employee ID and first name from EMP_NAMES table,
we can provide an alias by placing the alias (ename in this case) in front of
the table name separated by a blank space. Then you can also write EMP_ID
field name as ename. EMP_ID which tells the system that you need to pick up
the EMP_ID field from ename table which is an alias of EMP_NAMES table. It
makes no sense to do this when there is only one table in the select statement
since the system knows it needs to get this field from the only available table
but when you have two or more, it is always better to use aliases since if you
have the same field in multiple tables, you can specify using aliases where to
pick it from.
The execution provides the same result as if there were no alias.

I hope this was easy to understand and follow. Stay tuned for the next part
where we learn aggregation functions and further clauses for WHERE
conditions.
Core SAP HANA SQL script concepts- GROUP BY, IN &
BETWEEN,ORDER BY

Welcome to the fourth installment of this SAP HANA SQL Scripts core concepts
section where we try to understand how to use SQL GROUP BY to aggregate
rows of data, learn IN and BETWEEN clauses for applying filter with multiple
values and also how to sort data using ORDER BY.

Let’s start aggregating…SAP HANA SQL Script GROUP BY

We now take up a new requirement – one to see the salary by country. This
information is present in table EMP_SALARY. So we pick up this information
using the SELECT statement as always.

We see the below output with 50 rows of information.

But what we if we need to have the countries displayed with a sum of their
total salaries paid. In this case, we need to use the SUM aggregate function.
And whenever you use aggregate functions, any field that is not in an
aggregate function must be in a GROUP BY clause after the table name.

The execution result is as below. We now see the sum of all salaries of each
country. I have not used an alias for this summation field but feel free to do
so.
GROUP BY with HAVING Clause

When you have a GROUP BY in your statement, you have an option to use the
HAVING clause also. It is similar to a WHERE clause but only works with
aggregate functions. So if you want to display the countries and their collective
aggregations of salary paid but you only want to display the countries which
paid less than 40,000 in total. In this case, the total isn’t available in the table
but also needs to be calculated on run time. Hence we use the HAVING clause
with the same aggregation as shown below.

Execution produces the below results which are clearly filtered out by the
HAVING clause as per the requirement.
Now what if you needed to add a filter too, the WHERE condition should be
ahead of the GROUP BY. In this example, I add the filter to pick only Country
codes IN and FR. As always, field names are in double quotes, character values
in single quotes and integer values without quotes.

Executing this provides the below result.

IN Operator
Note that you could have also written this filter as “COUNTRY” IN (‘IN’,’FR’)
which on execution would produce the same result.

As expected, the result is:

BETWEEN Operator
Sometimes we would also need to filter a range of values. For this use the
BETWEEN..AND keyword combination. To filter employee IDs between 1001
and 1010, use this keyword combination as shown below. Note again that the
field in filter doesn’t need to be in the list of fields that are selected for output.

On execution, we get the below result.

Order by in SQL Statement

To understand order by, let’s switch to the EMP_NAMES table for a short while.
It contains the Employee ID, first name and last name. There comes a need
at times to sort one or more columns of data. We use the Order by clause for
this operation. Order by should appear at the end of your select statement
after your WHERE, GROUP BY and HAVING clauses if any or all of them exist.
Let’s now sort the EMP_NAMES table by First Name in ascending sequence.
The default sort of order by is ascending and hence in this case you don’t have
to write a keyword for this specifically. Hence we write the below statement.

The execution result proves that the result set got sorted by first name of the
employees
Now, to make the ORDER BY do a descending sort, all you have to do is add
the desc keyword at the end as shown below.

As shown below, the sort works as expected.

As explained before, the sequence of keywords is

SELECT <fields> from <table> WHERE <conditions> GROUP

BY<every_unaggeggated_field> HAVING<aggregation_filter> ORDER BY
<sort_sequence>

Not all of these clauses are mandatory. It varies as per what you need to fulfil
your requirement.

Now, coming back to the EMP_SALARY table, we now demonstrate a simple

example where all of these clauses are used.

Here we need the sum of salaries by country for only those employees who’s
ID is between 1005 and 1030. When the sum is done, the display should show
only those countries who’s salary summation is less than 50000. Also, the
overall list must be sorted descending by country ID. The below code reflects
these statements.
The result set is as shown below.

I hope this was easy to understand and follow. Read the next “Part-5” tutorial
to understand how you can use string functions to manipulate the character
data sets.

Core SAP HANA SQL script concepts- String

Functions
Welcome to the fifth installment of this SAP HANA SQL Scripts core concepts
section where we try to understand how to use different string functions and
operators to manipulate character data sets.

The String manipulation begins…

It’s now time to pick up the EMP_NAMES table which contains the first name
and last name of each employee. Let’s first do a simple SELECT to see the
table data entirely.

The executed result looks as the below.

HANA SQL PIPE || Operator

Now, with this table, we can look at some more features of character string
selection. What if you have these fields, first name and last name but you
need a field that shows full name? Obviously, full name is the concatenation
of these two fields. To concatenate two strings, we use a pipe operator || as
shown below.
The result is shown as below. We’re almost there but sadly, we missed the
blank space between first and last name.

But don’t worry, this is an easy fix. Concatenate first name, a blank space and
the last name field using two pipe operators as shown below As you can clearly
see, we get a blank space by enclosing a space between two single quotes.

Now as seen from the execution result, the full name looks as we expect. The
column name is still the formula which we already know how to fix.
Alias comes to the rescue! We use the AS keyword in front of the formula and
name it FULL_NAME.

As seen below, this looks good now.

There are many string functions that you can use in SAP HANA SQL Script.
Use this link from SAP to know more.

Let me know if the link stops working in the future by using the comment
section below.

SQL String functions – LEFT ()

The below example explains how to display left section of any character string
in SELECT statement. The LEFT function helps us achieve this. Just use
LEFT(<field_name>,<nr_of_characters>). The below use of function asks
HANA to pick up the first 2 characters from first name field.

The below execution confirms the result.

Now, let’s consider a use-case where you need a field INITIAL which should
display the first character from first name and last names fields concatenated
together. To do this, we use both the concepts we just learnt. As seen below,
we use LEFT to cut the first name only to the first letter and then concatenate
the last name with a similar LEFT function using a pipe operator.

The execution looks as expected.

I hope this was easy to understand and follow. In the next tutorial, we learn
how to represent and report on data that is “nothing” and understand how we
can negate an entire selection filter using the NOT keyword.
Core SAP HANA SQL script concepts- SQL NULL
and NOT
Welcome to the sixth installment of this SAP HANA SQL Scripts core concepts
section where we try to understand the concept of nothing – SQL NULL and
also later dwell into negating WHERE conditions with the NOT operator.

Welcome to nothingness – SQL NULL

NULL is nothing. NULL is not zero or a blank space. It is just the absence of
anything or in simpler terms – nothing. Sometimes, for some rows of data,
we don’t have the corresponding information. Databases fill these cells of
missing information with a symbol for NULL. In SAP HANA, NULL is symbolized
in data preview as ? by default. This can be changed in the HANA Studio
preferences but you don’t need to do so. The ? is not a question mark. It is
just a symbolic representation of NULL.

To demonstrate this, I created a new table which shows the employee ID and
the corresponding years of experience he/she has in another column called
YRS_EXP. But while filling the data, I filled in a few nulls which you can see in
the data preview below marked by arrows. The ? here means that the data for
these employee IDs is simply missing in the database.
If you wish to do some SELECT operation to check which of these employees
have years of experience missing in their data, you just have to filter on
YRS_EXP to be equal to NULL. NULL is the keyword used to denote the null
values in SQL SCRIPT codes. Using this logic, we draft the below code.

On execution, we get nothing. Something’s wrong. YRS_EXP = NULL doesn’t

seem to be the correct statement.
In these cases, you have to use IS NULL keyword instead of = NULL. This is
one of the mistakes beginners tend to make.

Upon execution, we get the results we require correctly this time.

1
NOT KEYWORD
The NOT is an interesting keyword. It negates every filter just by being placed
in front of it. For example, instead of IS NULL in the previous example, we
write IS NOT NULL in the filter and execute it.

The result shows 46

entries thereby excluding the 4 null values that our previous SELECT showed.

I hope this was easy to understand and follow. Please read the next part of
these tutorials to learn how to update, delete data in a table and at the end,
we learn how to delete the tables too.
Core SAP HANA SQL script concepts- SQL
UPDATE,DELETE,DROP
Welcome to the seventh installment of this SAP HANA SQL Scripts core
concepts section where we learn how to manipulate data using SQL UPDATE,
delete certain data using SQL DELETE and obliterate the table from existence
using SQL DROP statement.

Till now, we have learnt the INSERT which inserts new data into a table and
also SELECT which picks up data from the table. Now we learn the next
database statement which is SQL UPDATE. This is used rarely but is still
important. It is used to change existing values in a SAP HANA table.

SQL UPDATE – Time for changes…

Let’s take an example. We know from our examples that the employee
experience for employee 1001 is NULL. Let’s change it to a value of 10.
Template is:

UPDATE <table_name>

SET <field_name> = <value/ field/ formula>

WHERE <condition>

The SET keyword specifies what goes into the result cell(s). This can be a
constant value or can also take values from other fields of the table.
Otherwise, a formula can also be used to calculate the result to be passed on.
The WHERE condition specifies the data sets on which this resultant value
needs to be applied to.
The drafted code looks as below. The execution is confirmed by the log at the
bottom of the screen which says Rows affected: 1.
To check if the SQL UPDATE operation worked, let’s do a SELECT on the
EMP_EXP table for only employee ID 1001.

As seen from the execution result, the years of experience is now updated as
10 now. It was NULL (marked as ?) earlier. Thus our SQL UPDATE works as
expected.
DELETE STATEMENT HANA

Now that we are clear on SQL UPDATE, it’s time to let the inner demons out
and be a little destructive. In really rare circumstances, we require deletion of
certain rows of data from a table. This is done via a DELETE statement.
Template is:

DELETE FROM <table_name>

WHERE <optional_condition>

The condition you specify decides which rows will be updated. If you specify
no condition, all of the rows of data would be deleted. Only the table
structure/metadata would remain with no data.
Be really careful with this statement. Double check with your project leads
before you decide to do some deletions.
Let’s take an example to clear the data for employee ID 1001. We draft the
DELETE statement as below. The log shows that 1 row was affected which is
a positive confirmation.

Now let’s try to SELECT the one row for employee 1001
 .

On execution we see that no such record exists anymore and hence our
DELETE operation was successful.

Now, let’s get more destructive and run the delete without any conditions. As
you see the log tells us that the other 49 rows have also been now cleared off.
COUNT function
Instead of doing a SELECT to check if our DELETE worked, let’s try to count
the number of rows of data that are left in this table.
This is done using the COUNT function. COUNT() counts the number of rows
of data that exists for a particular field. Also, using COUNT(*) would count the
total number of rows existing in a table. To check the count of rows existing
in this table after everything has been deleted from it, we do a COUNT(*) on
EMP_EXP table as shown below.

The output as 0 confirms that our DELETION is successful.

Let’s take another example where we want to do a quick analysis of how many
unique values for GENDER exist in EMP_MASTER table. So we run a
COUNT(GENDER) on our EMP_MASTER table as shown below.

The result is 50. Which is because we told the system to pick up the number
of times a gender value occurred in the table but did not tell it to pick up only
unique occurrences.

To pick up counts of unique gender values, we add another keyword –

DISTINCT in front of the field name GENDER and run this again.
This time we get the correct result that there are two distinct gender values
that exist in the table(which we already know is ‘Male’ and ‘Female’)

DROP statement

The final and most destructive statement in SAP HANA is DROP. It deletes the
table from existence along with its structure/metadata and data.
Template for this is:

DROP TABLE <table_name>

As simple as this statement is, this should be used with the utmost caution.
Otherwise, this is one of those things that can cost you your job if you go
wrong with it in a real time project. Always be 110% sure that you wish to
DROP something before you actually proceed with it.
The below screenshot shows the statement being executed and the EMP_EXP
table being dropped.
I hope this was easy to understand and follow. The next part will enlighten
you on the SQL CASE statement which you can use to fill different values to
the same field based on unique conditions. Also we learn the concept of
SELECT inside SELECT.. a nested SELECT. Be sure to check it out.
Core SAP HANA SQL script concepts- SQL CASE
& Nested SELECT
Welcome to the eighth installment of this SAP HANA SQL Scripts core concepts
section where we learn how to pick up different data for the same field when
provided with unique conditions with SQL CASE statement. We will also then
understand the concept of having a SELECT statement acting as a filter to
other SELECT statement which is also called the NESTED SELECT.

SQL CASE STATEMENT in HANA SQL SELECT

The case statement is very useful and important thing to learn. There are
many instances where a new field needs to be calculated but the values may
vary with the values of other fields in the row of data. Let’s say we have an
example where we need a new field CATEGORY which should be filled with
value ALPHA if the employee ID is between 1001 and 1005. Otherwise for all
other employee IDs, fill this field with value DELTA.

The case statement has the below syntax.

CASE

WHEN <condition1>

THEN <result1>

WHEN <condition2>

THEN <result2>

WHEN <conditionN>

THEN <resultN>

ELSE <exception_result >

END
As seen above, it should start with keyword CASE and should finish at keyword
END.

<condition> stands for the check that must be performed.

<result> stands for the corresponding output for its

condition.

<exception result> for all cases that don’t fall under any conditions
mentioned in WHEN statement

Using this logic, we build the below select statement for this requirement and
give the result an alias of CATEGORY.

Below is the result of execution. After employee ID 1005, you can see the
CATEGORY switch to value DELTA as per our logic.
If you have more conditions, feel free to add more WHEN… THEN conditions
as per your requirement. In addition to the condition in our example above, if
you need a CATEGORY displayed called GAMMA for employee ID between 1006
and 1008, just add another WHEN.. THEN as shown below.

The below execution confirms that the code works well.

SQL NESTED SELECT

Many a times, you come across a requirement which asks you to pick up
something from a table based on a list coming in from another table. That is
where you can use nested selects to make sure your dataset is filtered
accordingly. For example, let’s say you need the list of all employees and their
first and last names whose age is greater than 25. Now we know that the first
and last names exist in EMP_NAMES table but the age is present in the
EMP_MASTER. You can also do the INNER JOIN here but since we don’t need
any field from the EMP_MASTER in the output, a JOIN is not required. You can
write the SELECT as shown below. The select in brackets executes first
producing a list of employee IDs whose age is more than 25. And then this list
is fed into the IN filter of the outer SELECT to get the required data.
On execution, the below result set is produced.

In our next and final SQL core concepts Part 9 tutorial, we understand the
most important concept – SQL JOIN and SQL UNION. Be sure to check it out.
SQL Join Types in SAP
Hello one and all. Welcome to the next edition of our SAP HANA Tutorial
series. This is another one of our checkpoint courses where we understand
one of the basic concepts of database design – SQL Join Types in SAP. Since
this is a common tutorial, all of these types may not be applicable to ABAP,
BW and Enterprise HANA. Each of them supports some of these which we will
understand in each of those courses individually. But for now, this tutorial is
to understand what joins mean.

Most of the times, one table does not contain all the data needed for analyzing
the problem. You might have to look into other tables to find the other fields
that you need. The primary way to achieve this is via joins. But there are
different types of joins. Let’s look at each of them with an example.

Fruits make everything simple..even SQL Join

Types
Five students in a class are asked to input their favorite fruit and vegetable
into two database tables. They are free to not enter any fruit or vegetable if
they don’t like any.

Table 1: The Fruit table

Student Fruit

Shyam Mango

John Banana

David Orange

Maria Apple

Table 2: The Vege-Table

Student Vegetable
 Shyam Potato

David Carrot

Maria Peas

Lia Radish

After the data entry is complete, we can see that Lia doesn’t like any fruit and
John doesn’t like any vegetable apparently.

SAP SQL Join Types #1 : Inner Join

Now, let’s say our first analytical requirement is to get a singular view of all
the students with fields (Student, Fruit, Vegetable) who like at least one fruit
and at least one vegetable. This is a classic example of an INNER JOIN.

Student Fruit Vegetable

Shyam Mango Potato

David Orange Carrot

Maria Apple Peas

An INNER JOIN returns a view with result set that is common between all
involved tables.

SAP SQL Join Types #2: Left Outer Join

The next requirement we have is to get a singular view of all the students with
fields (Student, Fruit, Vegetable) who like at least one fruit even if they don’t
like any vegetables. This type of join is a LEFT OUTER JOIN.

Student Fruit Vegetables

Shyam Mango Potato

John Banana ?

David Orange Carrot

 Maria Apple Peas

A LEFT OUTER JOIN returns all the entries in the left table but only matching
entries in the right

Note: The ‘?’ in the data is a NULL entry. As discussed earlier, NULL denotes
the existence of nothing. NULL is not blank or zero. It’s just nothing. Since
John likes no vegetables, a null value is placed there. In SAP BW and ABAP
output, NULL maybe represented by ‘#’ whereas in Enterprise HANA, ‘?’ is
the default display of NULL values.

SAP SQL Join Types #3: Right Outer Join

Next, we now require a singular view of all the students with fields (Student,
Fruit, Vegetable) who like at least one vegetable even if they don’t like any
fruits. This is exactly the converse of our previous requirement. This type of
join is a RIGHT OUTER JOIN.

Student Vegetables Fruit

Shyam Potato Mango

David Carrot Orange

Maria Peas Apple

Lia Radish ?

A RIGHT OUTER JOIN returns all the entries in the right table but only
matching entries in the left

Note: This is kind of a redundant type of join as the position of these tables
can be reversed and a LEFT OUTER JOIN can be applied to achieve the same
results. For this same reason, RIGHT OUTER JOIN is rarely used and is also
considered a bad practice in terms of performance of SAP HANA views. So try
to avoid using it.

Student Fruit Vegetables

Shyam Mango Potato

John Banana ?
 David Orange Carrot

Maria Apple Peas

Lia ? Radish

SAP SQL Join Types #4: Full Outer Join

Next, we require the data of all the students with fields (Student, Fruit,
Vegetable) who like at least one fruit or at least one vegetable. This is a classic
example of an FULL OUTER JOIN.

Student Fruit Vegetables

Shyam Mango Potato

John Banana ?

David Orange Carrot

Maria Apple Peas

Lia ? Radish

A FULL OUTER JOIN returns all the data from all involved tables regardless
of whether they have matching values or not on the join condition.

This one is rarely used as we never usually need all the key values from both
tables. As seen from the example, this results in a lot of nulls. Nevertheless,
it is a rare need.

The main type of JOINS actually used in real time scenarios are INNER JOINS
and LEFT OUTER JOINS. LEFT OUTER JOINS are the preferred join type in
terms of performance as they require only scanning of one table to complete
the join.

SAP HANA SQL Script Specific Join Types

There are two other join types available in enterprise HANA called
REFERENTIAL JOIN and TEXT JOIN. A REFERENTIAL JOIN is the same
as an INNER JOIN but better in performance but should only be selected
when it is sure that the referential integrity is maintained which in regular
human English translates to mean that all values for the join condition fields in
the left table exist in the right table for their corresponding fields as well. This
is very difficult to guarantee and hence due to this involved risk, a
REFERENTIAL JOIN is best left untouched.

A TEXT JOIN is used for tables containing descriptions of fields. The text
tables/descriptive tables are always kept on the right hand side and their
language column needs to be specified.

A TEXT JOIN usually looks like the below.

Key Table:

Vegetable
Student
code

Shyam PO

David CA

Text Table:

Vegetable
Description Language
Code

PO Potato EN

PO Kartoffel DE

CA Carrot EN

CA Karotte DE

which join to become the below table with descriptions of the same vegetable
in two different languages – English and German

Vegetable
Student Description Language
Code

Shyam PO Potato EN

Shyam PO Kartoffel DE
 David CA Carrot EN

David CA Karotte DE

Note: As you might have already noticed, I have referred to the resultant
output of joining tables as “Views”. Views is the actual technical term for this
result set. A view is just a virtual output of a join and is not persisted/stored
on the disk like a table. The result set of a view only exists during run-time.

SAP HANA SQL script concepts- SQL JOIN,

UNION, UNION ALL
Welcome to the final installment of this SAP HANA SQL Scripts core concepts
section where we learn how to pick up related data from different tables using
SQL JOIN. We also learn the concept of combining data sets using the SQL
UNION whilst understanding the difference between UNION and UNION ALL.

SQL JOIN
Before we get started on this section, please read the article on SQL JOIN
explaining joins and join types in SAP HANA.

Joins are created when the information we need is split across different tables.
For example, if there is a requirement to provide the employee ID, first name,
email and age of an employee, we know that the data exists but exists in two
separate tables, namely EMP_NAMES and EMP_MASTER.
The basic template for an SQL JOIN is:

SELECT <field_names> from

<left_table> <left_alias>

<right_table> <right_alias>

ON

<join_condition>

Note: You can add WHERE, GROUP BY, HAVING and ORDER BY clause as per
the requirement just like a regular SELECT condition.

As you might have read in my document on JOINS, the order of tables matters
a lot in case of LEFT and RIGHT OUTER JOINS but not in INNER JOIN. The
LEFT table dictates the list of key field values for which corresponding values
must be picked up from the table on the right side in a LEFT OUTER JOIN. The
opposite is true for a RIGHT OUTER JOIN.

When you have two or more tables, using aliases are important for both
readability and coding. Each time you write a field name from a table, use it
with the alias name as <alias>.<field_name> so that SAP HANA knows which
table it comes from.

After the ON keyword, specify the join condition which would be the
relationship between the fields that connect these two tables.

So the first thing to do is to decide the type of SQL join you wish to use. Most
times, a join would be LEFT OUTER JOINs or INNER JOINS. In our case, both
tables contain the same key entries – 50 values of employee ID so it doesn’t
matter which one we keep on the left or right. Even if we do a LEFT OUTER or
an INNER JOIN, both would return the same values in this case. Now, the
decision boils down to performance. Which one of these joins would be faster?
The answer is a LEFT OUTER JOIN. As discussed in our SQL JOIN
document, LEFT OUTER JOIN only has to scan the key values on the left side
table and picks up the corresponding entries on the right table. An INNER JOIN
on the other hand scans both sides.

Using this logic, we draft the below code. As you can see below, I give aliases
to the two tables involved as ename and emast (you can call them anything
but it should look relevant to your table name). We know that EMP_ID exists
in both tables. As a thumb rule, try to always pick up common fields from the
left table. After the ON keyword, we specify the join condition as
ename.EMP_ID = emast.EMP_ID which means that the connecting fields of
these two tables is the EMP_ID.

On execution, we see the output as if all this data was coming in from a single
table.

As explained earlier, you can add more conditions to this statement like any
other SELECT statement that we learnt till now. Below, we see an example
where we display only the earlier statement with values for which age is more
than or equal to 25 and then we order it by employee ID in descending format.
The output filters and sorts as below.

If you are starting to find this complicated, please try this on your system
and try changing the clauses, play around using your creativity to modify
these select statements and their outputs to get a better feel of how things
work. Don’t worry with some practice, you will find these really easy.

Now, in practical real time project scenarios, there are often more than two
tables involved in joins. For example, if we had a requirement to enhance the
above join with the salary field from EMP_SALARY table, we would need to
add another join condition below the first one but before the WHERE clause.

After the first join executes, the result executes as the left side of the next
join. In this way you can add as many joins as required. All their WHERE
conditions should come in at the end. This new draft would look like the below.
Leave a comment in the comment section below if you feel you have questions
on this topic.

The output now looks as the below.

You can add more formulas to this statement like the BONUS and
FULL_NAME calculations we did earlier.
We have the new fields as shown below, You can change the position of
these columns by simply moving it to the position you like in the SELECT
statement.

UNIONS
DIFFERENCE BETWEEN UNION AND UNION ALL IN SAP HANA

Another important operation when in data modelling is UNION. When you

have two or more separate lists of data having the same or similar fields,
you might want to combine them both vertically into a unified single list. For
example, let’s say you need to pick the first five entries of employee IDs
from EMP_SALARY table where country code is in the list IN, CA or US. Also
you need a similar list for country codes DE, FR or CH. When you have these
lists, it is required to combine these five from each result set into one single
set of 10. In such a case, we write the below code.
Result is as shown below. We have the first 5 records from each of the two
result sets.

Now we change the code slightly and also include IN as a filter in the second
select statement. This now means that IN records may occur in both result
sets. Let’s see the execution results.

As seen below, five and five don’t always equal ten. There were some
records that were common between the first five and the next five and their
duplicates got removed/aggregated. That is how a UNION works.
Now what if you don’t want this aggregation to happen. What do you do?

The answer comes in the form of UNION ALL. It does the same work as
UNION whilst not removing any duplicates/aggregation operation. Let’s now
see the same example with UNION ALL. Try executing it.

In the result we see the two entries that have duplicates. Row 1 and 2 are
the same as row 6 and 7. These were aggregated during the UNION
operation earlier but remain in the output in case of UNION ALL.
Now you might be thinking as to why would someone preserve duplicates.
What’s the point of doing so. The answer is that usually we wouldn’t want to
have duplicates in result set and should be using UNION. But whenever you
are sure that the two datasets involved in the UNION will have distinct
datasets and have no duplicates, always do UNION ALL since it doesn’t waste
time and system resources to remove duplicates and hence is faster in terms
of performance.

I hope this was easy to understand and follow. Stay tuned for the next set of
HANA SQL Tutorials.
SAP HANA Scripted Calculation
View – Part 1
At this point starts the most important parts of the HANA SQL Tutorial series.
The SAP HANA scripted calculation view is used to code the logic required in
SQL. Usually, this is done whenever the logic was too complicated to be
achieved by the nodes available in Graphical views or if the SQL view would
be a better performant in terms of time taken in execution.

Some projects however, create all data models with SAP HANA SQL scripts.
This allows flexibility and often simpler data models. However, starting from
SAP HANA Service Pack 11 (SP11), SAP recommends that you create graphical
views for your requirements and only choose scripted data models when a
graphical one can’t handle the requirement.

SAP HANA Scripted Calculation View would be the most common SQL based
data model you would find in existing projects worldwide. But, with SAP HANA
SP11 which is still quite new, SAP recommends to now use Table Functions
whenever Graphical views aren’t enough for your modeling needs. Therefore,
the Scripted Calculation view is also on its way to become something of the
past.

However, this move to table functions might take a while since most
consultants aren’t aware of table functions or even the updates from SAP on
this matter. Also, changing something that is working fine on a system isn’t
recommended unless there is a risk foreseen. Hence, SAP HANA scripted
calculation view is a really important topic to be properly understood.

Creating our first SAP HANA Scripted Calculation view

In our SAP HANA Tutorial section, we had created a SAP HANA graphical
calculation view called SALES_VIEW as shown below. This view combined
sales order header table VBAK and Sales order item table VBAP with each
other. Take a look at the design before we proceed further.
Let’s re-create the same logic in a SAP HANA Scripted calculation view. To do
this, right click on your package to select New Calculation view.
A familiar window would open up asking for some information.
Fill in some name. We have used SALES_SCRIPT as both the “Name” and
“Label” of the SAP HANA Scripted Calculation View. The default type of
Calculation view is a graphical one and hence, you would have to use the drop
down as shown below to switch the view type to SQL Script.
After selecting the Calculation view type, the overall settings should look as
below. Once done, press the Finish button.
The below window opens up. As you can see, the SAP HANA Scripted
Calculation view is a two node structure. The bottom node is where you write
the HANA SQL Script and the semantics node, as always is the output node.
There cannot be any additional nodes in this data flow. All the necessary logic
must be written into the Script_View node.
Click on the Script_View node to bring up the Details Pane. This is where you
would proceed to write the HANA SQL Script code for your logic that needs to
be implemented. In addition to this, the Output pane on the right provides the
list of output columns as well as input parameters associated with this view.

Firstly, we need to define the output fields for this view along with their data
types and length. Click on the Green plus symbol to add the output fields.

This will open the below window. Here, you can manually type in the name of
the output field and the other details or if the fields come from an existing
table, you can import the name and datatypes by clicking on the blue button
marked below.
1

This brings up the find window where you need to specify the name of the
object you wish to search for. Firstly, let’s search for the VBAK table. Write
VBAK in the search bar and wait for the search to finish. As seen below, there
are two VBAK tables in different schemas. The one we use in our tutorials is
ECC_DATA. Pick this one and click on Next.
This now brings up the below window where all the fields of table VBAK are
listed on the left side and the right side represents the fields added to our
target (Scripted view node).
Select the fields you need to move to the target and press the “Add” button
highlighted below. Alternatively, you can do them one by one too to test your
patience. We select a few fields from this table as shown below.
Once the Add button is pressed, the selected fields move to the right side as
illustrated. Press Finish.
This brings us back to the below window which now says “Edit Columns”
instead of “Create columns” when we started. As seen below, the field names,
datatypes and lengths of these fields have been successfully copied from the
VBAK table to the node output.

Please note that the view currently has no relation or link to the table VBAK.
This was just a quick method to copy the field information. You could have
chosen to do it manually as well but this is a safer method since you copy this
information directly from the table which you will use later on in your code.
This ensures that there won’t be any datatype mismatches due to human
error.
As per the original requirement, we require to JOIN tables VBAK and VBAP.
But for the sake of simplicity, let’s start out by creating a view which only
displays the output of table VBAK for the fields we selected.

Press OK to confirm these fields.

Once done, you would now notice the field names are displayed under the
“Columns” list.

Now, it’s time to write the code for this logic.

The code needs to be written between the BEGIN and END block shown below.

The output of the logic must have the same names and the same order as the
fields we added to the “Columns” folder. This final output must feed into the
var_out table variable as seen in the code.

What this means is that the structure of this var_out is what we defined by
defining the “Columns” list a while back.

To start, remove the two dots in front of the var_out (marked by the green
arrow). They are of no use.
Once you have the code looking as below, we can start adding the logic.

In this case, a simple SELECT statement which we have already learnt is

added. We select the fields we need from table VBAK in schema ECC_DATA
whilst maintaining the same order of fields as in the output we defined for this
node.

Make sure you add a semicolon at the end of the statement. This may not
sound like much of an advice but trust me, beginners make smaller mistakes
like these more often than you might imagine.
Activate the view and do a data preview. As seen below, the view output is as
per our expectations.

Congratulations! You just made your first SAP HANA Scripted Calculation View.

In the next part of this tutorial, we will work on introducing the VBAP table
using SQL script JOIN in our Calculation view.
SAP HANA Calculation view using SQL script
– Part 2
Welcome to the second part of the tutorial introducing SAP HANA Calculation
view using SQL script . If you’ve landed directly on this page, I recommend
starting from the first tutorial for better understanding. Now, as per our
original requirement, we would need a view consisting of a join between VBAK
and VBAP. This tutorial aims at achieving this requirement.

SAP HANA Calculation view using SQL script- Table Aliases

To start off, let’s use a table alias to our existing code. We give the alias “VK”
to VBAK (you can use any name). Thus, a field MANDT could also be called
VK.MANDT which tells the system that the field MANDT comes from table VK
(which is the alias for VBAK).

As you might note, there’s only one table and it makes no sense to use an
alias. It’s just redundant. Well, we will include VBAP in the next few steps and
this was just to show you how the code evolves step by step in slow motion.

SAP HANA Calculation view using SQL script- Adding

columns
Again, before we proceed further, let’s add the fields we need from VBAP table
to the output list. To do this, right click on the “Columns” folder and click on
“Edit Columns”.
This brings up our friendly window which already has the fields we used from
VBAK.

In a familiar fashion as earlier, click on the blue icon to Add more columns.

Again, type in the table name which is VBAP in our case. Select the one in
schema ECC_DATA.
This brings up all the fields of VBAP. We select a few fields from here too.
Press Add to send them to the output.
Once the “Add” button is pressed, the fields selected move to the target
section. Press the Finish button to continue. Be careful not to check the
“Replace existing columns in output” checkbox. This would remove the
existing fields before adding the new ones which is not what we want here.

As seen below, the new fields have been added below the ones we already
had. Again, I would like to remind you that what we did just now was just to
copy the field names and their datatype details from table VBAP. These steps
did not establish any kind of link to the table VBAP whatsoever.

Press OK to add these columns to our scripted node.

AS seen on the right side, you now have the new fields in the “Column” folder.
Notice that the icons in front of the new fields are different. As soon as you
activate/validate the view, HANA would give them the standard blue icons if
they are dimensions (master data) or the orange bars (transaction data).

At this point, if you try to activate this view, it would fail. This is due to the
fact that the variable var_out has now 3 new fields which are not defined in
the output select statement.
Now, proceeding with our requirement, which was to join tables VBAK and
VBAP, we already have the code for output of the required VBAK fields. Also,
we added the alias for table VBAK and modified the field names to
accommodate this alias.

SAP HANA Calculation view using SQL script- Joining the

second table
Next, specify the type of join and the table you are joining it with.

In this case, we use a left outer join to the VBAP table under the ECC_DATA
schema. Also, we give the table an alias VP. The new lines of code added are
marked in red below.
Next, let’s select the fields we need from VBAP table and add them to the
output list of the SELECT statement in the same order as specified in the
“Columns” folder.

Next, we need to specify how these tables are being joined. In other words,
we specify the join condition or the relationship between these tables. Most
tables you receive from SAP Application sources are cross-client and contain
the field MANDT. This field should always be involved in the join if both tables
have it. To understand the concept of what cross-client means, it’s explained
in one of the sections in a separate tutorial which you can access by clicking
here.

The ON keyword marks the start of a join condition. VK.MANDT = VP.MANDT

tells the system that we need to join the tables based on rows in VBAK to rows
in VBAP where the MANDT values match. Since we used an alias, the table
names were not needed to be written.

As explained, this join condition should always be the first one to be applied
whenever the sources are SAP tables containing this field.

Add the next condition using the AND operator. The sales document number
(VBELN) is the primary key connecting both the tables. Add that condition as
shown below and that would complete your join condition. Make sure you have
a semicolon at the end. Save and activate this SAP HANA Scripted Calculation
View.
Execute a data preview and check the raw data tab. You would see that the
join has resulted in a singular view of the data set as we expected.

This concludes our tutorial on joining two tables via SAP HANA Calculation
view using SQL script.
SAP HANA Scripted Calculation View – Part
3
In real time projects, usually this scripted calculation view is the base for
building reports. The reporting team can have a hard time understanding
these field names. To be honest the HANA development team wouldn’t know
the descriptions of most of them too. Before we start, as a general disclaimer,
if you’ve landed directly on this page, I recommend starting from the first
tutorial for better understanding.

These names are actually all meaningful words in German. For example, the
VBELN field that we have been using is short for Vertriebsbelegnummer which
of course means Sales document number. For those of us less enlightened in
German, we usually proceed to rename these fields using field aliases which
we learnt earlier.

The names we use will be derived from their actual descriptions in SAP source
system tables.

Do check them out at these links – Click here for VBAK and Click here for
VBAP.

Also, in a view, it’s usually good to have the Sales Document number (VBELN)
and the Item number (POSNR) shown next to each other. Since they came
from different tables in our example, they are a bit far apart right now. Let’s
proceed to fix this too along with the naming.

Renaming & Ordering fields in SAP HANA Scripted

Calculation view
Below is the code we had already written in the scripted calculation view from
the previous steps we performed to join tables VBAK and VBAK. The code has
been now organized a bit better now by giving each field a separate line. There
is no difference in the functionality. It’s just that the following steps that we
do will look neater once there’s enough space next to the field name.
As explained earlier, the var_out should have the same structure (name and
sequence) of the fields in the “Columns” folder. So let’s first edit the “Columns”
to reflect the field names and the order we require. To achieve this, right click
on “Columns” and select “Edit Columns”.
This opens up our familiar Column list where you can edit the properties you
wish to. In each row, remove the field name in the “Name” column and provide
a better and meaningful label instead.

As seen below, all 9 labels were changed to something which can be much
easily read by anyone who uses this view for reporting purposes or any further
database modelling. As everything good in life, this good practice comes with
a warning.

Try to keep these names consistent.

For example, the field VBELN is a commonly used field. This field is present in
many tables and thus would propagate to many views. Each view might
rename it to maybe SALES_DOC or DOC_NO or DOCUMENT or oranges or
apples or whatever. Bad synchronization between field names in these cases
can cause confusions in development and or debugging a problem whenever
it arises.
You have been warned.

Next, we would like the ITEM field (renamed from field POSNR) to move next
to the SALES_DOC field (Renamed from VBELN). Thus, we need it to move
from position 7 to position 3.

To achieve this great feat, click on the ITEM row which you wish to move and
then click on the UP arrow which is ironically shown below by my red arrow
pointing down.

A single click on this arrow moves the row one position up. My amazing
mathematical skills tell me that in our case, we would require 4 clicks to
reach the third position.

After 4 clicks, ITEM is next to SALES_DOC as we intended. Press OK to confirm

the field list.
As seen on the right side, the “Columns” folder now displays the correct order
and better field names of these fields.
Now, add the table alias to each field using the AS keyword as learnt earlier.
This would rename each of these fields to the alias name. Ignore the order of
the fields in this step.

Now, to correct the order, just cut and paste the line containing the POSNR
(or the ITEM as per the new alias name) to the position just below the VBELN
(or the SALES_DOC as per the new alias).

Thus, the field names and order again match the “Columns” folder and the
universe is in complete harmony once again.

Once done rejoicing the moment, save and activate the view.
Execute a data preview. As seen below, the data is shown perfectly with easily
understandable field names. Also, SALES_DOC is next to ITEM for easier
analysis.

This finishes the third part of this tutorial series on the introduction to scripted
calculation view. Read on to learn more about applying filters, variables and
input parameters.
SAP HANA Scripted Calculation View Filters
Welcome again to the next SAP HANA Scripted Calculation View tutorial series.
In this one, we learn how to use SAP HANA Calculation view filters, SAP HANA
Variables and SAP HANA Input Parameters. This is the first tutorial in this
series and will focus just on SAP HANA Calculation view filters.

Let’s continue with the SAP HANA Scripted calculation view from our previous
tutorial. This code joined the VBAK and VBAP table and picked up a few fields
from each. But in usual project scenarios, we rarely need all the data for
reporting. Usually, there are filters that help us pick up data which is relevant
to the report we wish to eventually build. This reduces the size of the output
data set thereby increasing the performance of this data model and the reports
on top of it.

1
Application of SAP HANA Calculation view filters
As an example, let’s filter the data for Document type = ‘TA’. This field in the
table is AUART. So, at the end of the select query, add a where condition to
mark this SAP HANA Calculation view filter as shown below.

Save and activate the view.

Execute the view and perform a data preview. Switch to Distinct Values tab
and drag DOC_TYPE to the right pane. As seen below, the only values that
exist are ‘TA’. Thus our filter has worked.
1

Please note that a field that has not selected in the output of the select
statement can also be used as a filtering criterial. For example, the field
CMKUA is not in the list of output columns for our SELECT statement but we
can still filter on that value so that the join is performed only between the
filtered datasets. In this example, we add an AND operator because this is the
second filtering condition. Then we write CMKUA != 0. The composite sign !=
is used to denote the “not equal to” functionality.

Save and activate the calculation view. This SAP HANA Calculation view filter
now gets applied.
Help this website grow by sharing this document on social media by using the
icons below.

SAP HANA Scripted Calculation View

variables
Welcome to the new tutorial on SAP HANA scripted calculation view
variables. When the filters need to be applied after the end of execution of the
entire logic in a dynamic manner, variables are used. This means that when
you apply a variable filter on a field, there will be a pop-up window whenever
the data preview is done which asks for value(s) to the user on which the filter
needs to be applied.

Since variables are applied after the end of script logic, it can only be created
on the Semantics node. Due to this fact, variables in SAP HANA Scripted
calculation views are created in the same ways as they are done in Graphical
calculation views.
Creating an SAP HANA Scripted Calculation view variable
To create one, first click on the Semantics node to select it and then click on
the green Plus button marked below.

This brings up the below window which asks for some details like the technical
name and description of this variable, the attribute(field) on which it is applied
to and more.
1

We provide the name and description both as V_MATERIAL and also apply it
to the field MATERIAL_NR.

We allow multiple entries and make it non-mandatory. This means that any
number of values can be entered in the variable pop-up for this material
number filter at runtime but even if you don’t enter any values, the view would
still execute since the variable is not mandatory. Press Ok when done.

Save and activate the view.

Run a data preview on the view. Now, instead of the data preview options,
you would get a pop-up window asking for a material number as filter.
A filter value is entered as HT-1011 as an example. Press Ok.
1

In the raw data tab of the data preview, you can now see that only 7 rows
were retrieved because there were only these rows corresponded to the
material number we selected in the variable filter screen.
Hopefully, the concept of SAP HANA Scripted Calculation view variables is clear
to you now. Read on to the next tutorial to learn more about Input parameters
in SAP HANA.