An operator is a special character used primarily in SQL statement’s with WHERE clause to perform operation, such as comparisons and arithmetic operations. They are used to pass conditions in a SQL query.

Operator types given below can be used in SQL statements in HANA −

Arithmetic Operators
Comparison/Relational Operators
Logical Operators
Set Operators

Arithmetic Operators

Arithmetic operators are used to perform simple calculation functions like addition, subtraction, multiplication, division and percentage.

Operator	Description
+	Addition − Adds values on either side of the operator
–	Subtraction − Subtracts right hand operand from left hand operand
*	Multiplication − Multiplies values on either side of the operator
/	Division − Divides left hand operand by right hand operand
%	Modulus − Divides left hand operand by right hand operand and returns remainder

Comparison Operators

Comparison operators are used to compare the values in SQL statement.

Operator	Description
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

Logical operators

Logical operators are used to pass multiple conditions in SQL statement or are used to manipulate the results of conditions.

Operator	Description
ALL	The ALL Operator is used to compare a value to all values in another value set.
AND	The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.
ANY	The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN	The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS	The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN	The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE	The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg − NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	The OR operator is used to compare multiple conditions in an SQL statement’s WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Set Operators

Set operators are used to combine results of two queries into a single result. Data type should be same for both the tables.

UNION − It combines the results of two or more Select statements. However it will eliminate duplicate rows.
UNION ALL − This operator is similar to Union but it also shows the duplicate rows.
INTERSECT − Intersect operation is used to combine the two SELECT statements, and it returns the records, which are common from both SELECT statements. In case of Intersect, the number of columns and datatype must be same in both the tables.
MINUS − Minus operation combines result of two SELECT statements and return only those results, which belong to first set of result and eliminate the rows in second statement from the output of first.

SAP HANA – SQL Functions

There are various SQL functions provided by SAP HANA database −

Numeric Functions
String Functions
Fulltext Functions
Datetime Functions
Aggregate Functions
Data Type Conversion Functions
Window Functions
Series Data Functions
Miscellaneous Functions

Numeric Functions

These are inbuilt numeric functions in SQL and use in scripting. It takes numeric values or strings with numeric characters and return numeric values.

ABS − It returns the absolute value of a numeric argument.

Example − SELECT ABS (-1) “abs” FROM TEST;

abs

ACOS, ASIN, ATAN, ATAN2 (These functions return trigonometric value of the argument)

BINTOHEX − It converts a Binary value to a hexadecimal value.
BITAND − It performs an AND operation on bits of passed argument.
BITCOUNT − It performs the count of number of set bits in an argument.
BITNOT − It performs a bitwise NOT operation on the bits of argument.
BITOR − It perform an OR operation on bits of passed argument.
BITSET − It is used to set bits to 1 in <target_num> from the <start_bit> position.
BITUNSET − It is used to set bits to 0 in <target_num> from the <start_bit> position.
BITXOR − It performs XOR operation on bits of passed argument.
CEIL − It returns the first integer that is greater or equal to the passed value.
COS, COSH, COT ((These functions return trigonometric value of the argument)
EXP − It returns the result of the base of natural logarithms e raised to the power of passed value.
FLOOR − It returns the largest integer not greater than the numeric argument.
HEXTOBIN − It converts a hexadecimal value to a binary value.
LN − It returns the natural logarithm of the argument.
LOG − It returns the algorithm value of a passed positive value. Both base and log value should be positive.

Various other numeric functions can also be used − MOD, POWER, RAND, ROUND, SIGN, SIN, SINH, SQRT, TAN, TANH, UMINUS

String Functions

Various SQL string functions can be used in HANA with SQL scripting. Most common string functions are −

ASCII − It returns integer ASCII value of passed string.
CHAR − It returns the character associated with passed ASCII value.
CONCAT − It is Concatenation operator and returns the combined passed strings.
LCASE − It converts all character of a string to Lower case.
LEFT − It returns the first characters of a passed string as per mentioned value.
LENGTH − It returns the number of characters in passed string.
LOCATE − It returns the position of substring within passed string.
LOWER − It converts all characters in string to lowercase.
NCHAR − It returns the Unicode character with passed integer value.
REPLACE − It searches in passed original string for all occurrences of search string and replaces them with replace string.
RIGHT − It returns the rightmost passed value characters of mentioned string.
UPPER − It converts all characters in passed string to uppercase.
UCASE − It is identical to UPPER function. It converts all characters in passed string to uppercase.

Other string functions that can be used are − LPAD, LTRIM, RTRIM, STRTOBIN, SUBSTR_AFTER, SUBSTR_BEFORE, SUBSTRING, TRIM, UNICODE, RPAD, BINTOSTR

Date Time functions

There are various Date Time functions that can be used in HANA in SQL scripts. Most common Date Time functions are −

CURRENT_DATE − It returns the current local system date.
CURRENT_TIME − It returns the current local system time.
CURRENT_TIMESTAMP − It returns the current local system timestamp details (YYYY-MM-DD HH:MM:SS:FF).
CURRENT_UTCDATE − It returns current UTC (Greenwich Mean date) date.
CURRENT_UTCTIME − It returns current UTC (Greenwich Mean Time) time.
CURRENT_UTCTIMESTAMP
DAYOFMONTH − It returns the integer value of day in passed date in argument.
HOUR − It returns integer value of hour in passed time in argument.
YEAR − It returns the year value of passed date.

Other Date Time functions are − DAYOFYEAR, DAYNAME, DAYS_BETWEEN, EXTRACT, NANO100_BETWEEN, NEXT_DAY, NOW, QUARTER, SECOND, SECONDS_BETWEEN, UTCTOLOCAL, WEEK, WEEKDAY, WORKDAYS_BETWEEN, ISOWEEK, LAST_DAY, LOCALTOUTC, MINUTE, MONTH, MONTHNAME, ADD_DAYS, ADD_MONTHS, ADD_SECONDS, ADD_WORKDAYS

Data Type Conversion Functions

These functions are used to convert one data type to other or to perform a check if conversion is possible or not.

Most common data type conversion functions used in HANA in SQL scripts −

CAST − It returns the value of an expression converted to a supplied data type.
TO_ALPHANUM − It converts a passed value to an ALPHANUM data type
TO_REAL − It converts a value to a REAL data type.
TO_TIME − It converts a passed time string to the TIME data type.
TO_CLOB − It converts a value to a CLOB data type.

Other similar Data Type conversion functions are − TO_BIGINT, TO_BINARY, TO_BLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_FIXEDCHAR, TO_INT, TO_INTEGER, TO_NCLOB, TO_NVARCHAR, TO_TIMESTAMP, TO_TINYINT, TO_VARCHAR, TO_SECONDDATE, TO_SMALLDECIMAL, TO_SMALLINT

There are also various Windows and other miscellaneous functions that can be used in HANA SQL scripts.

Current_Schema − It returns a string containing the current schema name.
Session_User − It returns the user name of current session

So, this brings us to the end of blog. This Tecklearn ‘SQL Functions and Operators in SAP Hana’ blog helps you with commonly asked questions if you are looking out for a job in SAP Hana and SAP Domain. If you wish to learn SAP Hana and build a career in SAP domain, then check out our interactive, SAP HANA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sap-hana-training-certification/

SAP HANA Training

About the Course

SAP HANA is an in-memory computing application that is designed and developed to boost the business processes, deliver smart solutions, and simplify both hardware and software environments. Our Sap Hana Training course will help you understand and learn the fundamentals and will also felicitate on training hands-on for the better grasp on the course. Further, we have the highly qualified professionals who will train you about Sap Hana Studio, Modelling, Security features and its various other aspects. You will understand why SAP HANA is a fundamentally different database engine upon the completion of this SAP HANA course.

Why Should you take SAP HANA Training?

The average Sap Hana Consultant salary $165,750 per year or $85 per hour. (neuvoo.com).
SAP HANA is the highest growing technology; hence, there is no surprise in plenty of career opportunities in this field. Since it is one among the fastest-growing products in the history of SAP, it is considered by the industries as a ground-breaking key for in-memory databases.
SAP HANA currently has more than 6,500 customers globally.

What you will Learn in this Course?

Introduction to SAP HANA

Fundamentals of SAP HANA
Capabilities of SAP HANA
Limitations of SAP HANA

Key Features of SAP HANA

Key Features: High Performance functionalities In-Memory computing, Columnar store database, Data Compression and Massive Parallel Processing
Using SAP HANA for Non-SAP Applications

Architecture of SAP HANA

Detailed Architecture of SAP HANA Database
Concept of SAP HANA Landscapes and Scenarios

Overview of HANA Studio

SAP HANA System – Perspectives, Administration, Modelling, Development Plan
HANA Database SQL Basics and Database SQL Script
Types of statements and data types
Operators, expressions and basic query execution
Sub-queries, Types of Joins, Expressions and Loops
Catalog – Schema, Table, Views, Functions, Stored Procedures, Index, Synonyms, Sequences, Triggers

Data Provisioning

Data Provisioning with Flat File upload
Provisioning – SDA (Smart Data Access)
Joins Types in HANA

SAP HANA Modelling

Types of Models
Attribute Views, Joins and Using Filter Operations
Creating Restricted and Calculated Columns
Using Hierarchies
Analytic Views – Star Schema design and Multi-Dimensional Modelling
Variables and Input parameters

Calculation Views

Dimension Calculation View
Information View
SAP HANA Variables
Introduction to Input Parameters

SAP Project

Using HANA analytical view building of COPA (Controlling and Profitability Analysis) model
SAP HANA COPA for evaluation of market segments and classification of markets according to the products, customers or any combination of it

Dimension Calculation View

Dimension Calculation View – Star Join Calculation view
Using Projection, Join, Aggregation, Union and Rank

In-depth Modelling

Refactoring information models
Schema Mapping
Propagate to schematics and Show Lineage
Schema Mapping
Generating Time Data
Union Pruning
Using Time Travel
Migrating deprecated Information models
Using Currency Conversion
Web based Modelling Work bench

Analytic Privileges and Decision Tables

Classical Analytic Privileges
SQL Analytic Privileges
Dynamic analytic Privileges.
Turning Business Rules into Decision tables
Table Functions

SAP HANA Table Function

Query Optimizing Technique related to SAP HANA Tables
Web Based Modelling work bench

SAP HANA on Cloud

SAP Analytics with SAP Reporting environment SAP BOBJ – tools, WEBI, LUMIRA, DASHBOARD (integration between sap Hana and bob)

Advanced Topics Overview

SAP HANA Dynamic tiering
Delta Merge
SDI (Smart Data Integration)
SDA (Smart Data Access)

DATA Provisioning

SLT – SAP Landscape Transformation
BODS – Business Objects Data Services

Analytical Privileges

Classical XML Based Analytical Privileges
SQL Analytical Privileges

HANA Administration and Security

Hana Administration
Security in SAP HANA – User Management

Got a question for us? Please mention it in the comments section and we will get back to you.

787