- SAP HANA Tutorial
- SAP HANA - Home
- SAP HANA Introduction
- SAP HANA - Overview
- In-Memory Computing Engine
- SAP HANA - Studio
- Studio Administration View
- SAP HANA - System Monitor
- SAP HANA - Information Modeler
- SAP HANA - Core Architecture
- SAP HANA Modeling
- SAP HANA - Modeling
- SAP HANA - Data Warehouse
- SAP HANA - Tables
- SAP HANA - Packages
- SAP HANA - Attribute View
- SAP HANA - Analytic View
- SAP HANA - Calculation View
- SAP HANA - Analytic Privileges
- SAP HANA - Information Composer
- SAP HANA - Export and Import
- SAP HANA Reporting
- SAP HANA - Reporting View
- Bi 4.0 Connectivity to HANA Views
- SAP HANA - Crystal Reports
- SAP HANA - Excel Integration
- SAP HANA Security
- SAP HANA - Security Overview
- User Administration & Management
- SAP HANA - Authentications
- SAP HANA - Authorization methods
- SAP HANA - License Management
- SAP HANA - Auditing
- SAP HANA Data Replication
- SAP HANA - Data Replication Overview
- SAP HANA - ETL Based Replication
- SAP HANA - Log Based Replication
- SAP HANA - DXC Method
- SAP HANA - CTL Method
- SAP HANA - MDX Provider
- SAP HANA Monitoring
- SAP HANA - Monitoring and Alerting
- SAP HANA - Persistent Layer
- SAP HANA - Backup & Recovery
- SAP HANA - High Availability
- SAP HANA - Log Configuration
- SAP HANA SQL
- SAP HANA - SQL Overview
- SAP HANA - Data Types
- SAP HANA - SQL Operators
- SAP HANA - SQL Functions
- SAP HANA - SQL Expressions
- SAP HANA - SQL Stored Procedures
- SAP HANA - SQL Sequences
- SAP HANA - SQL Triggers
- SAP HANA - SQL Synonym
- SAP HANA - SQL Explain Plans
- SAP HANA - SQL Data Profiling
- SAP HANA - SQL Script
- SAP HANA Useful Resources
- SAP HANA - Questions and Answers
- SAP HANA - Quick Guide
- SAP HANA - Useful Resources
- SAP HANA - Discussion
SAP HANA - SQL Expressions
An Expression is used to evaluate a clause to return values. There are different SQL expressions that can be used in HANA −
- Case Expressions
- Function Expressions
- Aggregate Expressions
- Subqueries in Expressions
Case Expression
This is used to pass multiple conditions in a SQL expression. It allows the use of IF-ELSE-THEN logic without using procedures in SQL statements.
Example
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) ;
+----+----------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+---------+----------+