SELECT COUNT( CASE WHEN sal < 2000 THEN 1 ELSE NULL END ) count1,

COUNT( CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END ) count2,

COUNT( CASE WHEN sal > 4000 THEN 1 ELSE NULL END ) count3 FROM emp;

This statement will return count1, count2, count3 with integer value as per passed condition.

Function Expressions

Function expressions involve SQL inbuilt functions to be used in Expressions.

Aggregate Expressions

Aggregate functions are used to perform complex calculations like Sum, Percentage, Min, Max, Count, Mode, Median, etc. Aggregate Expression uses Aggregate functions to calculate single value from multiple values.

Aggregate Functions − Sum, Count, Minimum, Maximum. These are applied on measure values (facts) and It is always associated with a dimension.

Common aggregate functions include −

Average ()
Count ()
Maximum ()
Median ()
Minimum ()
Mode ()
Sum ()

Subqueries in Expressions

A subquery as an expression is a Select statement. When it is used in an expression, it returns a zero or a single value.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow −

Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
A subquery cannot be immediately enclosed in a set function.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

Example

SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ;

+—-+———-+—–+———+———-+

+—-+———-+—–+———+———-+

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+—-+———-+—–+———+———-+

SAP HANA – SQL Stored Procedures

A procedure allows you to group the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. These stored procedures are executed by applications to perform that task.

Stored Procedures can return data in the form of output parameters (integer or character) or a cursor variable. It can also result in set of Select statements, which are used by other Stored Procedures.

Stored Procedures are also used for performance optimization as it contains series of SQL statements and results from one set of statement determines next set of statements to be executed. Stored procedures prevent users to see the complexity and details of tables in a database. As Stored procedures contain certain business logic, so users need to execute or call the procedure name.

No need to keep reissuing the individual statements but can refer to the database procedure.

Sample Statement to Create Procedures

Create procedure prc_name (in inp integer, out opt "EFASION"."ARTICLE_LOOKUP")
as
begin
opt = select * from "EFASION"."ARTICLE_LOOKUP" where article_id = :inp ;
end;

SAP HANA – SQL Sequences

A sequence is a set of integers 1, 2, 3, that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.

Using AUTO_INCREMENT column

The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.

Example

Try out the following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because it is auto-incremented by MySQL.

CREATE TABLE INSECT -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY
(id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, 
# date collected -> origin VARCHAR(30) NOT NULL # where collected );

Query OK, 0 rows affected (0.02 sec)

INSERT INTO INSECT (id,name,date,origin) VALUES
(NULL,'housefly','2001-09-10','kitchen'),
(NULL,'millipede','2001-09-10','driveway'),
(NULL,'grasshopper','2001-09-10','front yard');

Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

SELECT * FROM INSECT ORDER BY id;

+----+-------------+------------+------------+

| id | name        | date       | origin     |

+----+-------------+------------+------------+

| 1  | housefly    | 2001-09-10 | kitchen    |

| 2  | millipede   | 2001-09-10 | driveway   |

| 3  | grasshopper | 2001-09-10 | front yard |

+----+-------------+------------+------------+

3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values

LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise, PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.

PERL Example

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle −

$dbh->do ("INSERT INTO INSECT (name,date,origin)

VALUES('moth','2001-09-14','windowsill')");

my $seq = $dbh->{mysql_insertid};

PHP Example

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ) −

mysql_query ("INSERT INTO INSECT (name,date,origin)

VALUES('moth','2001-09-14','windowsill')", $conn_id);

$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having join, with other table.

If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique −

ALTER TABLE INSECT DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value

By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100.

 CREATE TABLE INSECT
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

So, this brings us to the end of blog. This Tecklearn ‘SQL Expressions, Stored Procedures and Sequences 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.

525