Using data from a Table View
Overview of the SELECT statement
The SELECT statement is an SQL statement, which is used to read data from a database table or view. The SELECT statement is the central construct for reading access to data, of the Data Manipulation Language.
A SELECT statement must contain a SELECT and a FROM clause and can contain a WHERE, GROUP BY, HAVING and ORDER BY clause.
The SELECT Clause in its most simple form
The SELECT clause specifies the Projection List in other words which columns to read from the data or which columns to include in the result set of the SELECT statement.
The following options are also supported:
- You can include a single column in the projection list.
- You can include the same column in the projection list repeatedly.
- You can combine using the asterisk and explicitly named columns
- You can use the asterisk repeatedly in the projection list
Naming Columns of the result set
The column names of the result set can differ from the column names in the table selected from. In other words, you can “rename” result columns.
Columns name not enclosed in quotation marks are converted to upper case. A column name must be enclosed in quotation marks if one of the following applies:
- The column name should not be converted to upper case but be kept exactly as spelled in your statement.
- The column name contains a space, a punctuation mark, a colon or any other special character.
The SELECT Clause: Calculated Columns
The SELECT clause can also contain columns the value of which is computed dynamically, typically based on the values of other columns.
In the simplest form, these calculated columns can be literal values. Both string and numerical values are supported.
Note: String values are enclosed in single quotes. This can lead o confusion if column names are enclosed in quotes as well
SAP HANA supports using functions to calculate columns of the projection list. Different functions are available for different data types of the input columns and values.
SAP HANA supports a rich list of built-in functions. The following is only a selection.
|
Function |
Explanation |
|
ADD_YEAR(<data>,<number>) |
Adds a <number> of years to the specified <date> |
|
CURRENT_DATE |
Determine the current date |
|
ABS(<number>) |
Determine the absolute value |
|
ROUND(<number>) |
Rounds the <number> |
|
SQRT(<number>) |
Determines the square root of <numbers> |
|
UPPER(<string>) |
Converts the <String> to upper case |
|
SUBSTR(<string>,<start>,<len>) |
Extract a substring of <string> |
|
CONCAT(<string>,<string>) |
Concatenate two strings. Equivalent to the binary operator || |
|
COALESCE(<expressions>) |