SAP HANA Database System Views Reference En

(1)

PUBLIC

SAP HANA Platform SPS 06

Document Version: 1.0 - 26-06-2013

(2)

1 SQL Reference Manual. . . 9 1.1 Notation. . . 9 1.2 Introduction. . . 11 1.3 Data Types. . . 14 1.4 Predicates. . . 30 1.4.1 Comparison Predicates. . . 31 1.4.2 Range Predicate. . . 31 1.4.3 In Predicate. . . 32 1.4.4 Exists Predicate. . . .33 1.4.5 LIKE Predicate. . . 34 1.4.6 NULL Predicate. . . 35 1.4.7 CONTAINS Predicate. . . 35 1.5 Operators. . . 38 1.6 Expressions. . . 43 1.7 SQL Functions. . . .45

1.7.1 Data Type Conversion Functions. . . .45

1.7.2 DateTime Functions. . . 60 1.7.3 Fulltext Functions. . . 80 1.7.4 Number Functions. . . .84 1.7.5 String Functions. . . 102 1.7.6 Window Functions. . . 118 1.7.7 Miscellaneous Functions. . . .123 1.8 SQL Statements. . . .133

1.8.1 Data Definition Statements. . . 134

1.8.2 Data Manipulation Statements. . . 221

1.8.3 System Management Statements. . . 257

1.8.4 Session Management Statements. . . .285

1.8.5 Transaction Management Statements. . . .292

1.8.6 Access Control Statements. . . 298

1.8.7 Data Import Export Statements. . . 338

1.8.8 Procedural Statements. . . 349

1.9 Restrictions for SQL Statements. . . .369

1.10 SQL Error Codes. . . 371

2 System Views Reference. . . .390

2.1 Statistics Server Tables. . . 396

2.2 AFL_AREAS. . . 459

2.3 AFL_FUNCTIONS. . . 459

(3)

2.5 AFL_PACKAGES. . . 461 2.6 AUDIT_POLICIES. . . .462 2.7 AUTHORIZATION_GRAPH. . . 463 2.8 CONSTRAINTS. . . 465 2.9 CREDENTIALS. . . 466 2.10 CS_BO_VIEWS. . . 467 2.11 CS_FREESTYLE_COLUMNS. . . 468 2.12 CS_JOIN_CONDITIONS. . . 468 2.13 CS_JOIN_CONSTRAINTS. . . .469 2.14 CS_JOIN_PATHS. . . .470 2.15 CS_JOIN_TABLES. . . 471 2.16 CS_KEY_FIGURES. . . 472 2.17 CS_VIEW_COLUMNS. . . 473 2.18 DATA_STATISTICS. . . 473 2.19 DATA_TYPES. . . 476 2.20 EFFECTIVE_PRIVILEGES. . . 478 2.21 EFFECTIVE_ROLES. . . 479 2.22 EXPLAIN_PLAN_TABLE. . . 480 2.23 FULLTEXT_INDEXES. . . 482 2.24 FUNCTIONS. . . 484 2.25 FUNCTION_PARAMETERS. . . 485 2.26 GEOCODE_INDEXES. . . 486 2.27 GRANTED_PRIVILEGES. . . 487 2.28 GRANTED_ROLES. . . 488 2.29 INDEXES. . . 489 2.30 INDEX_COLUMNS. . . .490 2.31 INVALID_CONNECT_ATTEMPTS. . . .491 2.32 M_ATTACHED_STORAGES. . . 492 2.33 M_BACKUP_CATALOG. . . 492 2.34 M_BACKUP_CATALOG_FILES. . . 494 2.35 M_BACKUP_CONFIGURATION. . . 495 2.36 M_BLOCKED_TRANSACTIONS. . . 496 2.37 M_CACHES. . . .497 2.38 M_CACHES_RESET. . . 498 2.39 M_CACHE_ENTRIES. . . 499 2.40 M_CATALOG_MEMORY. . . 500 2.41 M_CE_CALCSCENARIOS. . . 501 2.42 M_CE_CALCVIEW_DEPENDENCIES. . . 502 2.43 M_CE_DEBUG_INFOS. . . 502 2.44 M_CE_DEBUG_JSONS. . . .503 2.45 M_CE_DEBUG_NODE_MAPPING. . . 504

(4)

2.46 M_CE_PLE_CALCSCENARIOS. . . 505 2.47 M_CLIENT_VERSIONS. . . .506 2.48 M_COMPACTION_THREAD. . . 507 2.49 M_CONDITIONAL_VARIABLES. . . 510 2.50 M_CONDITIONAL_VARIABLES_RESET. . . .511 2.51 M_CONNECTIONS. . . 512 2.52 M_CONNECTION_STATISTICS. . . 515 2.53 M_CONTAINER_DIRECTORY. . . 519 2.54 M_CONTAINER_NAME_DIRECTORY. . . 521 2.55 M_CONTEXT_MEMORY. . . 522 2.56 M_CONTEXT_MEMORY_RESET. . . 525 2.57 M_CONVERTER_STATISTICS. . . .525 2.58 M_CONVERTER_STATISTICS_RESET. . . 527 2.59 M_CS_ALL_COLUMNS. . . .528 2.60 M_CS_COLUMNS. . . 530 2.61 M_CS_PARTITIONS. . . 533 2.62 M_CS_TABLES. . . 534 2.63 M_CS_UNLOADS. . . .537 2.64 M_DATABASE. . . 538 2.65 M_DATABASE_HISTORY. . . 539 2.66 M_DATA_VOLUMES. . . 540 2.67 M_DATA_VOLUME_PAGE_STATISTICS. . . 541 2.68 M_DATA_VOLUME_PAGE_STATISTICS_RESET. . . 542 2.69 M_DATA_VOLUME_SUPERBLOCK_STATISTICS. . . 543 2.70 M_DEBUG_CONNECTIONS. . . 544 2.71 M_DEBUG_SESSIONS. . . 545 2.72 M_DELTA_MERGE_STATISTICS. . . 546 2.73 M_DISKS. . . 547 2.74 M_ERROR_CODES. . . .548 2.75 M_EVENTS. . . 549 2.76 M_EXPENSIVE_STATEMENTS. . . 550 2.77 M_EXPORT_BINARY_STATUS. . . 553 2.78 M_EXTRACTORS. . . .554 2.79 M_FEATURES. . . 555 2.80 M_FULLTEXT_QUEUES. . . 556 2.81 M_FUZZY_SEARCH_INDEXES. . . 557 2.82 M_GARBAGE_COLLECTION_STATISTICS. . . 558 2.83 M_GARBAGE_COLLECTION_STATISTICS_RESET. . . 561 2.84 M_HEAP_MEMORY. . . 562 2.85 M_HEAP_MEMORY_RESET. . . 564 2.86 M_HISTORY_INDEX_LAST_COMMIT_ID. . . 565

(5)

2.87 M_HOST_INFORMATION. . . 565 2.88 M_HOST_RESOURCE_UTILIZATION. . . 568 2.89 M_IMPORT_BINARY_STATUS. . . 570 2.90 M_INIFILES. . . 571 2.91 M_INIFILE_CONTENTS. . . .572 2.92 M_JOB_PROGRESS. . . 573 2.93 M_LANDSCAPE_HOST_CONFIGURATION. . . 575 2.94 M_LICENSE. . . .578 2.95 M_LICENSE_USAGE_HISTORY. . . 579 2.96 M_LIVECACHE_CONTAINER_STATISTICS. . . 580 2.97 M_LIVECACHE_CONTAINER_STATISTICS_RESET. . . 581 2.98 M_LIVECACHE_LOCKS. . . 582 2.99 M_LIVECACHE_LOCK_STATISTICS. . . 583 2.100 M_LIVECACHE_LOCK_STATISTICS_RESET. . . .585 2.101 M_LIVECACHE_OMS_VERSIONS. . . 586 2.102 M_LIVECACHE_PROCEDURE_STATISTICS. . . 587 2.103 M_LIVECACHE_PROCEDURE_STATISTICS_RESET. . . 596 2.104 M_LIVECACHE_SCHEMA_STATISTICS. . . 596 2.105 M_LIVECACHE_SCHEMA_STATISTICS_RESET. . . 597 2.106 M_LOCK_WAITS_STATISTICS. . . 597 2.107 M_LOG_BUFFERS. . . 598 2.108 M_LOG_BUFFERS_RESET. . . 600 2.109 M_LOG_PARTITIONS. . . 600 2.110 M_LOG_PARTITIONS_RESET. . . 604 2.111 M_LOG_SEGMENTS. . . 605 2.112 M_LOG_SEGMENTS_RESET. . . .607 2.113 M_MEMORY_OBJECTS. . . 607 2.114 M_MEMORY_OBJECTS_RESET. . . 609 2.115 M_MEMORY_OBJECT_DISPOSITIONS. . . 609 2.116 M_MERGED_TRACES. . . 611 2.117 M_MONITORS. . . 613 2.118 M_MONITOR_COLUMNS. . . 614 2.119 M_MUTEXES. . . 615 2.120 M_MUTEXES_RESET. . . 617 2.121 M_MVCC_TABLES. . . 617 2.122 M_OBJECT_LOCKS. . . 618 2.123 M_OBJECT_LOCK_STATISTICS. . . 618 2.124 M_OBJECT_LOCK_STATISTICS_RESET. . . 620 2.125 M_PAGEACCESS_STATISTICS. . . .620 2.126 M_PAGEACCESS_STATISTICS_RESET. . . 621 2.127 M_PASSWORD_POLICY. . . .622

(6)

2.128 M_PERFTRACE. . . 623 2.129 M_PERSISTENCE_ENCRYPTION_KEYS. . . .625 2.130 M_PERSISTENCE_ENCRYPTION_STATUS. . . 625 2.131 M_PERSISTENCE_MANAGERS. . . .626 2.132 M_PERSISTENCE_MANAGERS_RESET. . . 629 2.133 M_PLUGIN_MANIFESTS. . . 630 2.134 M_PREPARED_STATEMENTS. . . 630 2.135 M_READWRITELOCKS. . . .634 2.136 M_READWRITELOCKS_RESET. . . 637 2.137 M_RECORD_LOCKS. . . .637 2.138 M_REMOTE_CONNECTIONS. . . 638 2.139 M_REMOTE_STATEMENTS. . . 639 2.140 M_REORG_ALGORITHMS. . . 640 2.141 M_REPO_TRANSPORT_FILES. . . .641 2.142 M_RS_INDEXES. . . .641 2.143 M_RS_MEMORY. . . 644 2.144 M_RS_TABLES. . . 645 2.145 M_RS_TABLE_VERSION_STATISTICS. . . .646 2.146 M_SAVEPOINTS. . . 649 2.147 M_SAVEPOINT_STATISTICS. . . 652 2.148 M_SAVEPOINT_STATISTICS_RESET. . . 655 2.149 M_SEMAPHORES. . . 656 2.150 M_SEMAPHORES_RESET. . . 657 2.151 M_SEQUENCES. . . .658 2.152 M_SERVICES. . . 659 2.153 M_SERVICE_COMPONENT_MEMORY. . . .660 2.154 M_SERVICE_MEMORY. . . 661 2.155 M_SERVICE_NETWORK_IO. . . 663 2.156 M_SERVICE_NETWORK_IO_RESET. . . 664 2.157 M_SERVICE_REPLICATION. . . 664 2.158 M_SERVICE_STATISTICS. . . .667 2.159 M_SERVICE_THREADS. . . 669 2.160 M_SERVICE_THREAD_CALLSTACKS. . . .671 2.161 M_SERVICE_TRACES. . . .672 2.162 M_SERVICE_TYPES. . . 672 2.163 M_SESSION_CONTEXT. . . 673 2.164 M_SHARED_MEMORY. . . 678 2.165 M_SNAPSHOTS. . . 679 2.166 M_SQL_PLAN_CACHE. . . 679 2.167 M_SQL_PLAN_CACHE_OVERVIEW. . . 686 2.168 M_SQL_PLAN_CACHE_RESET. . . 688

(7)

2.169 M_SYSTEM_INFORMATION_STATEMENTS. . . .688 2.170 M_SYSTEM_LIMITS. . . .689 2.171 M_SYSTEM_OVERVIEW. . . .690 2.172 M_TABLES. . . 690 2.173 M_TABLE_LOB_FILES. . . 691 2.174 M_TABLE_LOCATIONS. . . 692 2.175 M_TABLE_PERSISTENCE_LOCATIONS. . . .693 2.176 M_TABLE_PERSISTENCE_STATISTICS. . . .694 2.177 M_TABLE_VIRTUAL_FILES. . . .695 2.178 M_TEMPORARY_OBJECT_DEPENDENCIES. . . .696 2.179 M_TEMPORARY_TABLES. . . .697 2.180 M_TEMPORARY_TABLE_COLUMNS. . . 699 2.181 M_TEMPORARY_VIEWS. . . 701 2.182 M_TEMPORARY_VIEW_COLUMNS. . . 702 2.183 M_TENANTS. . . .704 2.184 M_TEXT_ANALYSIS_LANGUAGES. . . 705 2.185 M_TEXT_ANALYSIS_MIME_TYPES. . . 705 2.186 M_TOPOLOGY_TREE. . . 706 2.187 M_TRACEFILES. . . 707 2.188 M_TRACEFILE_CONTENTS. . . 708 2.189 M_TRACE_CONFIGURATION. . . 709 2.190 M_TRACE_CONFIGURATION_RESET. . . .710 2.191 M_TRANSACTIONS. . . 711 2.192 M_UNDO_CLEANUP_FILES. . . .714 2.193 M_VERSION_MEMORY. . . 715 2.194 M_VOLUMES. . . 716 2.195 M_VOLUME_FILES. . . 717 2.196 M_VOLUME_IO_DETAILED_STATISTICS. . . 718 2.197 M_VOLUME_IO_DETAILED_STATISTICS_RESET. . . 721 2.198 M_VOLUME_IO_PERFORMANCE_STATISTICS. . . .722 2.199 M_VOLUME_IO_PERFORMANCE_STATISTICS_RESET. . . 722 2.200 M_VOLUME_IO_RETRY_STATISTICS. . . 722 2.201 M_VOLUME_IO_RETRY_STATISTICS_RESET. . . .725 2.202 M_VOLUME_IO_STATISTICS. . . 725 2.203 M_VOLUME_IO_STATISTICS_RESET. . . 725 2.204 M_VOLUME_IO_TOTAL_STATISTICS. . . 726 2.205 M_VOLUME_IO_TOTAL_STATISTICS_RESET. . . .728 2.206 M_VOLUME_SIZES. . . 728 2.207 M_WORKLOAD. . . 729 2.208 M_XS_APPLICATIONS. . . 730 2.209 M_XS_APPLICATION_ISSUES. . . 731

(8)

2.210 OBJECTS. . . 732 2.211 OBJECT_DEPENDENCIES. . . 732 2.212 OWNERSHIP. . . 733 2.213 PRIVILEGES. . . 734 2.214 PROCEDURES. . . 735 2.215 PROCEDURE_OBJECTS. . . 736 2.216 PROCEDURE_PARAMETERS. . . 737 2.217 QUERY_PLANS. . . 738 2.218 REFERENTIAL_CONSTRAINTS. . . 739 2.219 REMOTE_SOURCES. . . 740 2.220 REORG_OVERVIEW. . . 741 2.221 REORG_PLAN. . . 742 2.222 REORG_PLAN_INFOS. . . 743 2.223 REORG_STEPS. . . 744 2.224 ROLES. . . 745 2.225 SAML_PROVIDERS. . . 746 2.226 SAML_USER_MAPPINGS. . . 746 2.227 SCHEMAS. . . .747 2.228 SEARCH_RULE_SETS. . . 748 2.229 SEARCH_RULE_SET_CONDITIONS. . . .749 2.230 SEQUENCES. . . 750 2.231 SESSION_COOKIES. . . 751 2.232 SQLSCRIPT_TRACE. . . 751 2.233 STATISTICS. . . 752 2.234 STRUCTURED_PRIVILEGES. . . 753 2.235 SYNONYMS. . . 754 2.236 TABLES. . . 755 2.237 TABLE_COLUMNS. . . .758 2.238 TABLE_COLUMNS_ODBC. . . 760 2.239 TABLE_GROUPS. . . 762 2.240 TRANSACTION_HISTORY. . . 762 2.241 TRIGGERS. . . 763 2.242 USERS. . . 764 2.243 USER_PARAMETERS. . . .766 2.244 VIEWS. . . 766 2.245 VIEW_COLUMNS. . . .768 2.246 VIRTUAL_COLUMNS. . . 770 2.247 VIRTUAL_TABLES. . . 771 2.248 X509_USER_MAPPINGS. . . .771

(9)

1 SQL Reference Manual

● Notation ● Introduction ● Data Types ● Predicates ● Operators ● Expressions ● SQL Functions ● SQL Statements

● Restrictions for SQL Statements

● SQL Error Codes

1.1 Notation

This reference use BNF (Backus Naur Form) which is a notation technique used to define programming languages. BNF describes the syntax of a grammar using a set of production rules and a set of symbols.

Symbols used in BNF

Symbol Description

< > Angle brackets are used to surround the name of a syntactic element (BNF nonterminal) of the SQL language.

::= The definition operator is used to provide definitions of the element appeared on the left side of the operator in a production rule.

[ ] Square brackets are used to indicate optional elements in a formula. Optional elements may be specified or omitted.

{ } Braces group elements in a formula. Repetitive elements (zero or more elements) can be specified within brace symbols.

(10)

Symbol Description

| The alternative operator indicates that the portion of the formula following the bar is an alternative to the portion preceding the bar.

... The ellipsis indicates that the element may be repeated any number of times. If ellipsis appears after grouped elements specifying that the grouped elements enclosed with braces are repeated. If ellipsis appears after a single element, only that element is repeated. !! Introduces normal English text. This is used when the

definition of a syntactic element is not expressed in BNF.

BNF Lowest Terms Representations

Throughout the BNF used in this manual each syntax term will be defined to one of the lowest term representations shown below.

<digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 <letter> ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z <underscore> ::= _ <hash_symbol> ::= # <dollar_sign> ::= $

<any_character> !!= any character. <single_quote> ::= '

<double_quotes> ::= "

<simple_identifier> ::= {<letter> | <underscore>} [{<letter> | <digit> | <underscore> | <hash_symbol> | <dollar_sign>}...]

<special_identifier> ::= <double_quotes><any_character><double_quotes> <string_literal> ::= <single_quote><string_content><single_quote> <string_content> = {<letter> | <digit>}...

<identifier> ::= <letter> {<letter> | <digit>}... <password> ::= {<letter> | <digit>}...

(11)

<port_number> ::= <unsigned_integer> <sign> ::= + |

-<period> ::= .

<unsigned_integer> ::= <digit>...

<signed_integer> ::= [<sign>] <unsigned_integer>

<signed_numeric_literal> ::= [<sign>] <unsigned_numeric_literal> <unsigned_numeric_literal> ::= <exact_numeric_literal> |

<approximate_numeric_literal>

<exact_numeric_literal> ::= <unsigned_integer> [<period>[<unsigned_integer>]] | <period> <unsigned_integer>

<approximate_numeric_literal> ::= <mantissa>E<exponent> <mantissa> ::= <exact_numeric_literal>

<numeric_literal> ::= <signed_numeric_literal | <signed_integer>

1.2 Introduction

● SQL

● Supported Languages and Code Pages

● Comment

● Identifiers

● SQL Reserved Words

This chapter describes the SAP HANA Database implementation of Structured Query Language (SQL). It explains the characteristics of SQL, also how to manage comments and reserve words.

SQL

SQL stands for Structured Query Language. It is a standardized language for communicating with a relational database. SQL is used to retrieve, store or manipulate information in the database.

SQL statements perform the following tasks: ● Schema definition and manipulation ● Data manipulation

● System management ● Session management ● Transaction management

(12)

Supported Languages and Code Pages

The SAP HANA Database supports Unicode to allow the use of all languages in the Unicode Standard and 7 Bit ASCII code page without restriction.

Comment

You can add comments to improve readability and maintainability of your SQL statements. Comments are delimited in SQL statements as follows: <ul> <li> Double hyphens "--". Everything after the double hyphen until the end of a line is ignored by the SQL parser.</li> <li> "/*" and "*/". This style of commenting is used to place comments on multiple lines. All text between the opening "/*" and closing "*/" is ignored by the SQL parser.</li> </ul>

Identifiers

Syntax:

<simple_identifier> ::= {<letter>|<underscore>} [{<letter> | <digit> | <underscore> | <hash_symbol> | <dollar_sign>}...]

<special_identifier> ::= <double_quotes><any_character><double_quotes>

Identifiers are used to represent names used in SQL statement including table name, view name, synonym name, column name, index name, function name, procedure name, user name, role name, and so on. There are two kinds of identifiers, undelimited identifiers and delimited identifiers.

● Undelimited table and column names must start with a letter and cannot contain any symbols other than digits or an underscore "_".

● Delimited identifiers are enclosed in the delimiter, double quotes, then the identifier can contain any character including special characters. For example, "AB$%CD" is a valid identifier name.

● Limitations:

○ "_SYS_" is reserved exclusively for database engine, hence not allowed at the beginning of schema object names.

○ Role name and user name must be specified as undelimited identifiers. ○ Maximum length for the identifiers is 127 characters.

Single Quotation Mark

Single quotation marks are used to delimit string literals and single quotation mark itself can be represented using two single quotation marks.

(13)

Double Quotation Mark

Double quotation marks are used to delimit identifiers and double quotation mark itself can be represented using two double quotation marks.

SQL Reserved Words

Reserved words are words which have a special meaning to the SQL parser in the SAP HANA Database that cannot be used as a user-defined name. Reserved words should not be used in SQL statements for schema object names. If necessary, you can work around this limitation by delimiting a table or column name with double quotation marks.

The following table lists all the current and future reserved words for the SAP HANA Database.

Table 1: Table 1. Reserved Words

ALL ALTER AS BEFORE

BEGIN BOTH CASE CHAR

CONDITION CONNECT CROSS CUBE

CURRENT_CONNECTION CURRENT_DATE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURRENT_UTCDATE CURRENT_UTCTIME CURRENT_UTCTIMESTA

MP

CURRVAL CURSOR DECLARE

DISTINCT ELSE ELSEIF ELSIF

END EXCEPT EXCEPTION EXEC

FOR FROM FULL GROUP

HAVING IF IN INNER

INOUT INTERSECT INTO IS

JOIN LEADING LEFT LIMIT

LOOP MINUS NATURAL NEXTVAL

NULL ON ORDER OUT

PRIOR RETURN RETURNS REVERSE

RIGHT ROLLUP ROWID SELECT

(14)

SYSTIME SYSTIMESTAMP SYSUUID TOP

TRAILING UNION USING UTCDATE

UTCTIME UTCTIMESTAMP VALUES WHEN

WHERE WHILE WITH

1.3 Data Types

This section describes the data types used in the SAP HANA Database.

Data type specifies the characteristics of a data value. A special value of NULL is included in every data type to indicate the absence of a value. The following table shows the built-in data types available in the SAP HANA Database.

● Classification of Data Types

● Datetime Types

○ Date Formats

○ Time Formats

○ Timestamp Formats

○ Additional Formats

○ Supported Functions for Date/Time types

● Numeric Types

● Character String Types

● Binary Types

● Large Object (LOB) Types

● Mapping between SQL Data Type and Column Store Data Type

● Data Type Conversion

● Typed Constant

Classification of Data Types

In the SAP HANA Database each data type can be classified by its characteristic as follows:

Table 2: Table 2: Classification of data types

Classification Data Type

(15)

Classification Data Type

Numeric types TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE Character string types VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT

Binary types VARBINARY

Large Object types BLOB, CLOB, NCLOB, TEXT

Datetime Types

● DATE

The DATE data type consists of year, month, and day information to represent a date value. The default format for the DATE data type is 'YYYY-MM-DD'. YYYY represents the year, MM represents the month, and DD represents the day. The range of date value is 0001-01-01 through 9999-12-31.

● TIME

The TIME data type consists of hour, minute, and second to represent a time value. The default format for the TIME data type is 'HH24:MI:SS'. HH24 represents the hour from 0 to 24, MI represents the minute from 0 to 59, SS represents the second from 0 to 59.

● SECONDDATE

The SECONDDATE data type consists of year, month, day, hour, minute and second information to represent a date with time value. The default format for the SECONDDATE data type is 'YYYY-MM-DD HH24:MI:SS'. YYYY represents the year, MM represents the month, DD represents the day, HH24 represents hour, MI represents minute, and SS represents seconds. The range of date value is 0001-01-01 00:00:01 through 9999-12-31 24:00:00.

● TIMESTAMP

The TIMESTAMP data type consists of date and time information. Its default format is 'YYYY-MM-DD HH24:MI:SS.FF7'. FFn represents the fractional seconds where n indicates the number of digits in fractional part. . The range of the timestamp value is 0001-01-01 00:00:00.0000000 through 9999-12-31

23:59:59.9999999.

For details on supported formats for datetime types, refer to Table 4, Table 5, Table 6 and Table 7 below. Date Formats

The following date/time formats can be used when parsing a string into a date/time type and converting a date/ time type value into a string value. Please note that format for Timestamp is the combination of Date and Time with the additional support for fractional seconds.

(16)

Table 3: Table 4: Supported formats for Date

Format Description Examples

YYYY-MM-DD Default format INSERT INTO my_tbl VALUES ('1957-06-13');

YYYY/MM/DD YYYY/MM-DD YYYY-MM/DD

YYYY from 0001 to 9999, MM from 1 to 12, DD from 1 to 31. If year has less than four digits, month has less than two digits, or day has less than two digits, then values will be padded by one or more zeros. For example, a two digit year 45 will be saved as year 0045, a one digit month 9 will be saved as 09, and a one digit day 2 will be saved as 02.

INSERT INTO my_tbl VALUES ('1957-06-13');

INSERT INTO my_tbl VALUES ('1957/06/13');

INSERT INTO my_tbl VALUES ('1957/06-13');

INSERT INTO my_tbl VALUES ('1957-06/13');

YYYYMMDD ABAP Data Type, DATS format. INSERT INTO my_tbl VALUES ('19570613');

MON Abbreviated name of month. (JAN. ~ DEC.)

INSERT INTO my_tbl VALUES (TO_DATE('2040-Jan-10', 'YYYY-MON-DD'));

INSERT INTO my_tbl VALUES (TO_DATE('Jan-10', 'MON-DD')); MONTH Name of month. (JANUARY -

DECEMBER).

INSERT INTO my_tbl VALUES (TO_DATE('2040-January-10', 'YYYY-MONTH-DD'));

INSERT INTO my_tbl VALUES (TO_DATE('January-10', 'MONTH-DD'));

RM Roman numeral month (I-XII; JAN = I).

INSERT INTO my_tbl VALUES (TO_DATE('2040-I-10', 'YYYY-RM-DD'));

INSERT INTO my_tbl VALUES (TO_DATE('I-10', 'RM-DD'));

(17)

DDD Day of year (1-366). INSERT INTO my_tbl VALUES (TO_DATE('204', 'DDD')); INSERT INTO my_tbl VALUES (TO_DATE('2001-204','YYYY-DDD'));

Time Formats

Table 4: Table 5: Supported formats for Time

HH24:MI:SS Default format HH:MI[:SS][AM|PM]

HH12:MI[:SS][AM|PM] HH24:MI[:SS]

HH from 0 to 23. MI from 0 to 59. SS from 0 to 59. FFF from 0 to 999. If one digit hour, minute, second is specified, then 0 will be inserted into the value. For example, 9:9:9 will be saved as 09:09:09. HH12 indicates 12 hour clock and HH24 indicates 24 hour clock. AM or PM can be specified as a suffix to indicate the time value is before or after noon.

INSERT INTO my_tbl VALUES ('23:59:59');

INSERT INTO my_tbl VALUES ('3:47:39 AM');

INSERT INTO my_tbl VALUES ('9:9:9 AM');

INSERT INTO my_tbl VALUES (TO_TIME('11:59:59','HH12:MI:SS');

SSSSS Seconds past midnight (0-86399). INSERT INTO my_tbl VALUES (TO_TIME('12345', 'SSSSS')); Timestamp Formats

Table 5: Table 6: Supported formats for Timestamp

YYYY-MM-DD HH24:MI:SS.FF7 Default format

FF [1..7] Fractional seconds has the range 1 to 7 after the FF parameter to

INSERT INTO my_tbl VALUES (TO_TIMESTAMP('2011-05-11

(18)

specify the number of digits in the fractional second portion of the date time value returned. If a digit is not specified, the default value is used.

12:59.999','YYYY-MM-DD HH:SS.FF3'));

Additional Formats

Table 6: Table 7: Additional formats for Datetime

Format Description Example

D Day of week (1-7). TO_CHAR(CURRENT_TIMESTAMP,' D')

DAY Name of day (MONDAY - SUNDAY). TO_CHAR(CURRENT_TIMESTAMP,' DAY')

DY Abbreviated name of day (MON - SUN).

TO_CHAR(CURRENT_TIMESTAMP,' DY')

MON Abbreviated month name (JAN - DEC)

TO_CHAR(CURRENT_TIMESTAMP,' MON')

MONTH Full month name (JANUARY - DECEMBER)

TO_CHAR(CURRENT_TIMESTAMP,' MONTH')

RM Roman numeral month (I - XII; I is for January)

TO_CHAR(CURRENT_TIMESTAMP,' RM')

Q Quarter of year (1, 2, 3, 4) TO_CHAR(CURRENT_TIMESTAMP,' Q')

W Week of month (1-5). TO_CHAR(CURRENT_TIMESTAMP,' W')

WW Week of year (1-53). TO_CHAR(CURRENT_TIMESTAMP,' WW')

Supported Functions for Date/Time types ● ADD_DAYS ● ADD_MONTHS ● ADD_SECONDS ● ADD_YEARS ● COALESCE ● CURRENT_DATE ● CURRENT_TIME

(19)

● CURRENT_TIMESTAMP ● CURRENT_UTCDATE ● CURRENT_UTCTIME ● CURRENT_UTCTIMESTAMP ● DAYNAME ● DAYOFMONTH ● DAYOFYEAR ● DAYS_BETWEEN ● EXTRACT ● GREATEST ● GREATEST ● HOUR ● IFNULL ● ISOWEEK ● LAST_DAY ● LEAST ● LOCALTOUTC ● MINUTE ● MONTH ● MONTHNAME ● NEXT_DAY ● NULLIF ● QUARTER ● SECOND ● SECONDS_BETWEEN ● TO_DATE ● TO_DATS ● TO_TIME ● TO_TIMESTAMP ● UTCTOLOCAL ● WEEK ● WEEKDAY ● YEAR

Numeric Types

● TINYINT

The TINYINT data type stores an 8-bit unsigned integer. The minimum value is 0 and the maximum value is 255 for TINYINT.

(20)

The SMALLINT data type stores a 16-bit signed integer. The minimum value is -32,768 and the maximum value is 32,767 for SMALLINT.

● INTEGER

The INTEGER data type stores a 32-bit signed integer. The minimum value is -2,147,483,648 and the maximum value is 2,147,483,647 for INTEGER.

● BIGINT

The BIGINT data type stores a 64-bit signed integer. The minimum value is -9,223,372,036,854,775,808 and the maximum value is 9,223,372,036,854,775,807 for BIGINT.

● DECIMAL(precision, scale) or DEC(p,s)

DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. "p" specifies precision or the number of total digits (the sum of whole digits and fractional digits). "s" denotes scale or the number of fractional digits. For example, if a column is defined as DECIMAL(5, 4), the numbers 3.14, 3.1415, 3.141592 are stored in the column as 3.1400, 3.1415, 3.1415, respectively keeping the specified precision(5) and scale(4).

The precision p, can range from 1 to 34. The scale can range from 0 to p. If the scale is not specified, it defaults to 0.

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In this case, precision and scale can vary within the range 1 to 34 for precision and -6,111 to 6,176 for scale depending on the stored value.

Examples: 0.0000001234 (1234 x 10-10) has the precision 4 and the scale 10. 1.0000001234 (10000001234 x 10-10) has the precision 11 and scale 10. 1234000000 (1234x106) has the precision 4 and scale -6.

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In this case, precision and scale can vary within the range described above, 1~34 for precision and -6,111~6,176 for scale depending on the stored value.

● SMALLDECIMAL

The SMALLDECIMAL is a floating-point decimal number. The precision and scale can vary within the range, 1~16 for precision and -369~368 for scale depending on the stored value. SMALLDECIMAL is supported only on column store.

DECIMAL and SMALLDECIMAL are floating-point types. For instance, a decimal column can store any of 3.14, 3.1415, 3.141592 whilst maintaining their precision.

DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. For instance, 3.14, 3.1415, 3.141592 are stored in a decimal(5, 4) column as 3.1400, 3.1415, 3.1415, respectively keeping the specified precision(5) and scale(4).

● REAL

The REAL data type specifies a single-precision 32-bit floating-point number. ● DOUBLE

The DOUBLE data type specifies a single-precision 64-bit floating-point number. The minimum value is -1.79769 x 10308 and the maximum value is 1.79769x10308 . The smallest positive DOUBLE value is 2.2207x10-308 and the largest negative DOUBLE value is -2.2207x10-308.

(21)

The FLOAT(n) data type specifies a 32-bit or 64-bit real number, where n specifies the number of significant bits and can range between 1 and 53.

When you use the FLOAT(n) data type, if n is smaller than 25, it becomes a 32-bit REAL data type. If n is greater than or equal to 25, it then becomes a bit DOUBLE data type. If n is not declared, it becomes a 64-bit double data type by default.

Character String Types

The character string data types are used to store values that contain character strings. While VARCHAR data types contain ASCII character strings, NVARCHAR are used for storing Unicode character strings.

● VARCHAR

The VARCHAR(n) data type specifies a variable-length ASCII character string, where n indicates the maximum length and is an integer between 1 and 5000.

● NVARCHAR

The NVARCHAR(n) data type specifies a variable-length Unicode character set string, where n indicates the maximum length and is an integer between 1 and 5000.

● ALPHANUM

The ALPHANUM(n) data type specifies a variable-length character string which contains alpha-numeric characters, where n indicates the maximum length and is an integer between 1 and 127.

● SHORTTEXT

The SHORTTEXT(n) data type specifies veriable-length character string which supports textsearch-features and stringsearch-features. This is not a standalone sql-type. Selecting a SHORTTEXT(n)-column yields a column of type NVARCHAR(n).

<shorttext_type> ::= SHORTTEXT ( <unsigned_integer> ) <elem_list_shorttext> <elem_list_shorttext> ::= <elem_shorttext> [{, <elem_shorttext>}...]

<elem_shorttext> ::= <fulltext_elem> | SYNC[HRONOUS]

Binary Types

Binary types are used to store bytes of binary data. ● VARBINARY

The VARBINARY(n) data type is used to store binary data of a specified maximum length in bytes, where n indicates the maximum length and is an integer between 1 and 5000.

(22)

Large Object (LOB) Types

LOB (large objects) data types, CLOB, NCLOB and BLOB, are used to store a large amount of data such as text documents and images. The maximum size of an LOB is 2 GB.

● BLOB

The BLOB data type is used to store large binary data. ● CLOB

The CLOB data type is used to store large ASCII character data. ● NCLOB

The NCLOB data type is used to store a large Unicode character object. ● TEXT

The TEXT data type specifies which supports textsearch-features. This is not a standalone sql-type. Selecting a TEXT-column yields a column of type NCLOB.

<text_type> ::= TEXT <opt_fulltext_elem_list_text>

<opt_fulltext_elem_list_text> ::= <fulltext_elem_text> [{, <fulltext_elem_text>}...]

<fulltext_elem_text> ::= <fulltext_elem> | [SYNC[HRONOUS]

| [ASYNC[HRONOUS] FLUSH [QUEUE]

| EVERY <n> MINUTES [[OR] AFTER <m> DOCUMENTS] ] Syntax-rules common to TEXT and SHORTTEXT

<fulltext_elem> ::= LANGUAGE COLUMN <column_name>

| LANGUAGE DETECTION '(' <str_const_list> ')' | MIME TYPE COLUMN <column_name>

| FUZZY SEARCH INDEX [ON|OFF] | PHRASE INDEX RATIO [ON|OFF] | CONFIGURATION <str_const> | SEARCH ONLY [ON|OFF] | FAST PREPROCESS [ON|OFF]

LOB types are provided for storing and retrieving such large data. LOB types support the following operations. ● The length() function returns the LOB length in bytes.

● LIKE can be used to search LOB columns. The LOB types have the following restrictions:

● LOB columns cannot appear in ORDER BY or GROUP BY clauses. ● LOB columns cannot appear in FROM clauses as a join predicate.

● LOB columns cannot appear in WHERE clauses as a predicate except LIKE, CONTAINS, =, or <>. ● LOB columns cannot appear in SELECT clauses as an aggregate function argument.

● LOB columns cannot appear in SELECT DISTINCT clauses.

(23)

● LOB columns cannot be used as a primary key.

● LOB columns cannot be used in CREATE INDEX statements. ● LOB columns cannot be used in statistics update statements.

Mapping between SQL Data Type and Column Store Data Type

SQL Type Column Store Type

SQL Type Column Store Type Integer Types TINYINT, SMALLINT, INT CS_INT

BIGINT CS_FIXED(18,0) Approximate Types REAL CS_FLOAT

DOUBLE CS_DOUBLE

FLOAT CS_DOUBLE

FLOAT(p) CS_FLOAT, CS_DOUBLE Decimal Types DECIMAL CS_DECIMAL_FLOAT

DECIMAL(p,s) CS_FIXED(p-s,s) SMALLDECIMAL CS_SDFLOAT Character Types VARCHAR CS_STRING

NVARCHAR CS_STRING CLOB, NCLOB CS_STRING ALPHANUM CS_ALPHANUM

Binary Types BLOB CS_RAW

VARBINARY CS_RAW Date/Time Types DATE CS_DAYDATE

TIME CS_SECONDTIME

TIMESTAMP CS_LONGDATE SECONDDATE CS_SECONDDATE

(24)

Data Type Conversion

This section describes the data type conversion allowed in SAP HANA Database. ● Explicit type conversion

The type of an expression result, for example a field reference, a function on fields, or literals can be converted using the following functions: CAST, TO_ALPHANUM, TO_BIGINT, TO_VARBINARY, TO_BLOB, TO_CLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_INTEGER, TO_INT, TO_NCLOB,

TO_NVARCHAR, TO_REAL, TO_SECONDDATE, TO_SMALLINT, TO_TINYINT, TO_TIME, TO_TIMESTAMP, TO_VARCHAR.

● Implicit type conversion

When a given set of operand/argument types does not match what an operator/function expects, a type conversion is carried out by the SAP HANA Database. This conversion only occurs if a relevant conversion is available and if it makes the operation/function executable. For instance, a comparison of BIGINT and VARCHAR is performed by implicitly converting VARCHAR to BIGINT. The entire explicit conversions can be used for implicit conversion except for the TIME and TIMESTAMP data types. TIME and TIMESTAMP can be converted to each other using TO_TIME(TIMESTAMP) and TO_TIMESTAMP(TIME).

● Examples

Table 7: Table 8: Implicit Type conversion Examples

Input Expression Transformed Expression with Implicit Conversion

Input Expression Transformed Expression with Implicit Conversion BIGINT > VARCHAR BIGINT > BIGINT(VARCHAR)

BIGINT > DECIMAL DECIMAL(BIGINT) > DECIMAL TIMESTAMP > DATE TIMESTAMP > TIMESTAMP(DATE)

DATE > TIME Error because there is no conversion available between DATE and TIME

In the tables below,

● Boxes with "OK" means data type conversions are allowed without any checks.

● Boxes with "CHK" means the data type can be converted if the data is valid for the target type. ● Boxes with "-" indicates that data type conversion is not allowed.

The rules shown are applicable to both implicit and explicit conversion except for Time to Timestamp conversion. Only explicit conversions are allowed for converting the Time data type to Timestamp using the TO_TIMESTAMP or CAST functions.

(25)

Table 8: Table 9a: Data type conversion table Ta rg et / So ur ce tin yin t sm alli nt int eg er big int de ci ma l de ci ma l(p ,s) sm all de ci ma l re al do ubl e var ch ar nv arc ha r tin yin t sm alli nt int eg er big int de ci ma l de ci ma l(p ,s) sm all de ci ma l re al do ubl e var ch ar nv arc ha r Ta rge t/ So urc e tin yin t sm alli nt int eg er big int de ci ma l de ci ma l(p, s) sm all de ci ma l rea l do ubl e var ch ar nv arc har tin yin t - OK OK OK OK OK OK OK OK OK OK sm alli nt CH K - OK OK OK OK OK OK OK OK OK int eg er CH K CH K - OK OK OK OK OK OK OK OK big int CH K CH K CH K - OK CH K CH K CH K OK OK OK de ci ma l CH K CH K CH K CH K - CH K CH K CH K OK OK OK de ci ma l(p, s) CH K CH K CH K CH K CH K CH K CH K CH K CH K CH K OK sm all de ci CH K CH K CH K CH K OK CH K - CH K CH K OK OK

(26)

Ta rg et / So ur ce tin yin t sm alli nt int eg er big int de ci ma l de ci ma l(p ,s) sm all de ci ma l re al do ubl e var ch ar nv arc ha r tin yin t sm alli nt int eg er big int de ci ma l de ci ma l(p ,s) sm all de ci ma l re al do ubl e var ch ar nv arc ha r ma l rea l CH K CH K CH K CH K OK CH K CH K - OK OK OK do ubl e CH K CH K CH K CH K CH K CH K CH K CH K - OK OK var ch ar CH K CH K CH K CH K CH K CH K CH K CH K CH K - OK nv arc har CH K CH K CH K CH K CH K CH K CH K CH K CH K CH K

-Table 9: -Table 9b: Data type conversion table Target

/

Sourc e

time date secon ddate timest amp varcha r nvarch ar

time date secon ddate timest amp varcha r nvarch ar Target / Sourc e

time date secon ddate timest amp varcha r nvarch ar time - - - - OK OK date - - OK OK OK OK secon ddate

time date - timest amp

(27)

Target /

Sourc e

time date secon ddate timest amp varcha r nvarch ar

time date secon ddate timest amp varcha r nvarch ar timest amp

time date secon ddate - OK OK varcha r CHK CHK CHK CHK - OK nvarch ar CHK CHK CHK CHK CHK

-Table 10: -Table 9c: Data type conversion table Target/

Source

varbinary alphanum varchar nvarchar varbinary alphanum varchar nvarchar

Target/ Source

varbinary alphanum varchar nvarchar

varbinary - - - -alphanum - - OK OK varchar OK OK - OK nvarchar OK OK CHK -Data Type Precedence

This section describes the data type precedence implemented by the SAP HANA Database. Data type precedence specifies that the data type with lower precedence is converted to the data type with higher precedence.

Hig hest Low est High est TIM EST AMP SEC ON

(28)

Hig hest Low est DDA TE DAT E TIM E DOU BLE REA L DEC IMA L SMA LLD ECI MAL BIGI NT INT EGE R SMA LLIN T TIN YIN T NCL OB NVA RCH AR CLO B

(29)

Hig hest Low est VAR CHA R BLO B Low est VAR BIN ARY

Typed Constant

A constant is a symbol that represents a specific fixed data value. ● Character string contant

A character string constant is enclosed in single quotation marks. ○ 'Brian'

○ '100'

Unicode string has a similar format to character string but is preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase.

○ N'abc'

SELECT 'Brian' "character string 1", '100' "character string 2", N'abc' "unicode string" FROM DUMMY;

character string 1, character string 2, unicode string Brian, 100, abc

● Number constant

A number constant is represented by a string of numbers that are not enclosed in quotation marks. Numbers may contain a decimal point or scientific notation.

○ 123 ○ 123.4 ○ 1.234e2

A hexadecimal number constant is a string of hexadecimal numbers and has the prefix 0x. ○ 0x0abc

SELECT 123 "integer", 123.4 "decimal1", 1.234e2 "decimal2", 0x0abc "hexadecimal" FROM DUMMY;

(30)

integer, decimal1, decimal2, hexadecimal 123, 123.4, 123.4, 2748

● Binary string constant

A binary string has the prefix X and is a string of hexadecimal numbers that are enclosed in quotation marks. ○ X'00abcd'

○ x'dcba00'

SELECT X'00abcd' "binary string 1", x'dcba00' "binary string 2" FROM DUMMY; binary string 1, binary string 2

00ABCD, DCBA00

● Date/Time/Timestamp constant

Date, Time and Timestamp each have the following prefixes. ○ date'2010-01-01'

○ time'11:00:00.001'

○ timestamp'2011-12-31 23:59:59'

SELECT date'2010-01-01' "date", time'11:00:00.001' "time", timestamp'2011-12-31 23:59:59' "timestamp" FROM DUMMY;

date, time, timestamp

2010-01-01, 11:00:00, 2011-12-31 23:59:59.0

1.4 Predicates

A predicate is specified by combining one or more expressions, or logical operators, and returns one of the following logical/truth values: TRUE, FALSE, or UNKNOWN.

● Comparison Predicates ● Range Predicate ● In Predicate ● Exists Predicate ● LIKE Predicate ● NULL Predicate ● CONTAINS Predicate

(31)

1.4.1 Comparison Predicates

Syntax

<comparison_predicate> ::= <expression> { = | != | <> | > | < | >= | <= }

[ ANY | SOME | ALL ] { <expression_list> | <subquery> }

Syntax Elements

<expression_list> ::= <expression> [{, <expression>}...]

Expressions are either simple expression such as a character, a date or a number, or expression can also be a scalar subquery (see Expressions and Subquery).

ANY | SOME

Specifies that the comparison returns true if at least one value returned by the <subquery> or <expression_list> are true.

ALL

Specifies that the comparison returns true if the comparison of all values returned by the <subquery> or <expression_list> are true.

Description

Two values are compared using comparison predicates and the comparison returns true, false, or unknown.

1.4.2 Range Predicate

Syntax

(32)

Syntax Elements

NOT

Inverts the operation of the BETWEEN predicate. Specifies that a true will be returned when <expression1> is not in the range of values specified between <expression2> and <expression3>.

Description

Compares a value with a list of values within the provided range.

The range predicate returns true if <expression1> is within the range specified by <expression2> and <expression3>.

Note

A true will only be returned if <expression2> has a value less than or equal to <expression3>.

1.4.3 In Predicate

Syntax

<in_predicate> ::= <expression1> [NOT] IN { <expression_list> | <subquery> }

Syntax Elements

(33)

NOT

Inverts the operation of the IN predicate. Specifies that a true will be returned when <expression1> is not in the set of values specified by <expression_list> or the <subquery>.

Description

A value is compared with a specified set of values. A true will be returned if the value of <expression1> is found in the <expression_list> or <subquery>.

1.4.4 Exists Predicate

Syntax

<exists_predicate> ::= [NOT] EXISTS ( <subquery> )

Syntax Elements

NOT

Inverts the operation of the EXISTS predicate. Specifies that a true will be returned when the <subquery> returns an empty result set and false when the <subquery> returns a result set.

For information on subqueries, see Subquery

Description

Returns true if the <subquery> returns a result set that is not empty and returns false if the <subquery> returns an empty result set.

(34)

1.4.5 LIKE Predicate

Syntax

<like_predicate> ::= <expression1> [NOT] LIKE <expression2> [ESCAPE <expression3>]

Syntax Elements

NOT

Inverts the operation of the LIKE predicate. ESCAPE

Specifies an escape character to be used in the comparison string <expression2>. The escape character allows the underscore (_) or percentage sign (%) to be matched.

Description

The LIKE predicate performs string comparisons. <expression1> is tested for a pattern contained in

<expression2>. Wildcard characters ( % ) and ( _ ) can be used in the comparison string <expression2>. LIKE returns true if the pattern specified by expression2 is found.

The percentage sign (%) matches zero or more characters and underscore (_) matches exactly one character. To match a percent sign or underscore with the LIKE predicate, an escape character must be used.

Using ESCAPE <expression3>, you can specify the escape character that will be used allowing the underscore (_) or percentage sign (%) to be matched.

(35)

1.4.6 NULL Predicate

Syntax

<null_predicate> ::= <expression> IS [NOT] NULL

Syntax Elements

IS NULL

Returns true if the value of <expression> is NULL. IS NOT NULL

Returns true if the value of <expression> is not NULL.

Description

Performs a comparison of the value of an expression with NULL.

1.4.7 CONTAINS Predicate

The CONTAINS predicate is used to search for text-matches to a search string in subqueries. Syntax:

<contains_function> ::= CONTAINS '(' <contains_columns> ',' <search_string>')' | CONTAINS '(' <contains_columns> ',' <search_string> ',' <search_specifier> ')'

<contains_columns> ::= '*' | <column_name> | '(' <columnlist> ')' <search_string> ::= <string_const>

(36)

| <search_specifier2_list> <opt_search_specifier2_list> ::= empty

| <search_specifier2_list>

<search_type> ::= <exact_search> | <fuzzy_search> | <linguistic_search> <search_specifier2_list> ::= <search_specifier2>

| <search_specifier2_list> ',' <search_specifier2> <search_specifier2> := <weights> | <language>

<exact_search> ::= EXACT <fuzzy_search> ::= FUZZY

| FUZZY '(' <float_const> ')'

| FUZZY '(' <float_const> ',' <additional_params> ')' <linguistic_search> ::= LINGUISTIC

<weights> ::= WEIGHT '(' <float_const_list> ')' <language> :: LANGUAGE '(' <string_const> ')' <additional_params> ::= <string_const>

search_string

The freestyle-search-string format is used (eg. Peter "Palo Alto" OR Berlin -"SAP LABS" )

search_specifier

If the search_specifier clause is not specified EXACT is taken as default

EXACT

EXACT returns true for those records where exact matches of the searchterms are found in the search-attributes

FUZZY

FUZZY returns true for those records where words similar to the searchterms are found in the search-attributes (e.g. spelling errors will be ignored to a certain extent)

(37)

If float_const is omitted, then 0.8 is the default. This default can be overridden by defining parameter FUZZINESSTHRESHOLD supported by columnstore join-views

WEIGHT

If a weights list is specified, it must be the same size as the number of (expanded) columns in <contains_columns>

LANGUAGE

LANGUAGE is used during preprocessing of the search string and as a pre-search filter. Only documents which match the search string and the language specified are returned.

LINGUISTIC

LINGUISTIC returns true for those records where word-variants of the searchterms are found in the search-attributes (e.g. searching for 'cats' will also return records which contain 'cat')

Limitations: If there are multiple CONTAINS predicates specified in the where clause of a select statement, then only one of the predicates may consist of more than one column in the <contains_columns> list

The CONTAINS-predicate works only on column store tables (simple tables and join-views)

Examples: Exact search

select * from T where contains(column1, 'dog OR cat') -- EXACT is implicit select * from T where contains(column1, 'dog OR cat', EXACT)

select * from T where contains(column1, '"cats and dogs"') -- phrase search Fuzzy search

select * from T where contains(column1, 'catz', FUZZY(0.8)) Linguistic search:

select * from T where contains(column1, 'catz', LINGUISTIC) Freestyle search:

(38)

The freestyle-search is a search mulitple columns.

select * from T where CONTAINS( (column1,column2,column3), 'cats OR dogz', FUZZY(0.7))

1.5 Operators

● Unary and Binary Operators

● Operator Precedence ● Arithmetic Operators ● String Operators ● Comparsion Operators ● Logical Operators ● Set Operators

You can perform arithmetic operations in expressions by using operators. Operators can be used for calculation, value comparison or to assign values.

Unary and Binary Operators

Table 11: Table 10. Unary and binary operators

Operator Operation Format Description

Operator Operation Format Description Unary A unary operator applies

to one operand or a single value expression.

operator operand unary plus operator(+)

unary negation operator(-)

logical negation(NOT)

Binary Binary A binary operator applies to two operands or two value expressions.

operand1 operator operand2

multiplicative operators ( *, / )

(39)

Operator Operation Format Description

additive operators ( +,- )

comparison operators ( =,!=,<,>,<=,>=)

logical operators ( AND, OR )

Operator Precedence

An expression can use several operators. If the number of operators is greater than one then the SAP HANA Database will evaluate them in order of operator precedence. You can change the order of evaluation by using parentheses, as expressions contained within parentheses are always evaluated first.

If parentheses are not used, the operators have the precedence indicated by the table below. Please note, the SAP HANA Database will evaluate operators with equal precedence from left to right within an expression.

Table 12: Table 11. SQL operator precedence

Precedence Operator Operation

Highest () parentheses

+, - unary positive and negative operation *, / multiplication, division +, - addition, subtraction || concatenation =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN comparsion

(40)

Precedence Operator Operation

AND conjunction

Lowest OR disjunction

Arithmetic Operators

You use arithmetic operators to perform mathematical operations such as adding, subtracting, multiplying, dividing and negation of numeric values.

Table 13: Table 12. Arithmetic operators

Operator Description

-<expression> Negation. If the expression is the NULL value, the result is NULL.

<expression> + <expression> Addition. If either expression is the NULL value, the result is NULL.

<expression> - <expression> Subtraction. If either expression is the NULL value, the result is NULL.

<expression> * <expression> Multiplication. If either expression is NULL, the result is NULL.

<expression> / <expression> Division. If either expression is NULL, or if the second expression is 0, an error is returned.

String Operators

A concatenation operator combines two items such as strings, expressions or constants into one.

Table 14: Table 13. Concatenation operators

Operator Description

<expression> || <expression> String concatenation (two vertical bars). If either string is NULL, it returns NULL.

(41)

For VARCHAR or NVARCHAR type strings, leading or trailing spaces are kept. If either string is of data type NVARCHAR, the result has data type NVARCHAR and is limited to 5000 characters. The maximum length for VARCHAR concatenation is also limited to 5000 characters.

Comparsion Operators

Syntax:

<comparison_operation> ::= <expression1> <comparison_operator> <expression2>

Table 15: Table 14. Comparison operators

Operator Description Example

= Equal to SELECT * FROM students WHERE id = 25;

> Greater than SELECT * FROM students WHERE id > 25;

< Less than SELECT * FROM students WHERE id < 25;

>= Greater than or equal to SELECT * FROM students WHERE id >= 25;

<= Less than or equal to SELECT * FROM students WHERE id <= 25;

!=, <> Not equal SELECT * FROM students WHERE id != 25;

SELECT * FROM students WHERE id <> 25;

Logical Operators

Search conditions can be combined using AND or OR operators. You can also negate them using the NOT operator.

(42)

Table 16: Table 15. Logical operators

Operator Syntax Description

AND WHERE condition1 AND condition2 When using AND, the combined condition is TRUE if both conditions are TRUE, FALSE if either condition is FALSE, and UNKNOWN

otherwise.

OR WHERE condition1 OR condition2 When using OR, the combined condition is TRUE if either condition is TRUE, FALSE if both conditions are FALSE, and UNKNOWN otherwise.

NOT WHERE NOT condition The NOT operator is placed before a condition to negate the condition. The NOT condition is TRUE if condition is FALSE, FALSE if condition is TRUE, and UNKNOWN if condition is UNKNOWN.

Set Operators

The operators described in this section perform set operations on the results of two or more queries.

Table 17: Table 16. Set operators

Operator Returned Value

UNION Combines the results of two or more select statements or query expressions

UNION ALL Combines the results of two or more select statements or query expressions, including all duplicate rows. INTERSECT Combines the results of two or more select statements

or query expressions, and returns all common rows. EXCEPT Takes output from the first query and then removes

(43)

1.6 Expressions

● Case Expressions

● Function Expressions

● Aggregate Expressions

● Subqueries in Expressions

An expression is a clause that can be evaluated to return values.

Syntax

| <expression> <operator> <expression> | <variable_name>

| <constant>

| [<correlation_name>.]<column_name>

Case Expressions

A case expression allows the user to use IF - THEN - ELSE logic without using procedures in SQL statements. Syntax

<case_expression> ::= <simple_case_expression> | <search_case_expression> <simple_case_expression> ::=

CASE <expression>

WHEN <expression> THEN <expression> [{ WHEN <expression> THEN <expression>}…] [ ELSE <expression>]

END

<search_case_expression> > ::= CASE

WHEN <condition>THEN <expression>

[{ WHEN <condition> THEN <expression>}…] [ ELSE <expression>]

END

(44)

If the expression following the CASE statement is equal to the expression following the WHEN statement, then the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned if it exists.

Function Expressions

SQL built-in functions can be used as an expression. Syntax

<function_expression> ::= <function_name> ( <expression> [{, <expression>}...] )

Aggregate Expressions

An aggregate expression uses an aggregate function to calculate a single value from the values of multiple rows in a column.

Syntax

<aggregate_expression> ::= COUNT(*) | <agg_name> ( [ ALL | DISTINCT ] <expression> )

<agg_name> ::= COUNT | MIN | MAX | SUM | AVG | STDDEV | VAR

Aggregate name Description

COUNT Counts the number of rows returned by a query. COUNT(*) returns the number of rows, regardless of the value of those rows and including duplicate values. COUNT(<expression>) returns the number of non-NULL values for that expression returned by the query. MIN Returns the minimum value of expression.

MAX Returns the maximum value of expression. SUM Returns the sum of expression.

AVG Returns the arithmetical mean of expression.

STDDEV Returns the standard deviation of given expression as the square root of VARIANCE function.

VAR Returns the variance of expression as the square of standard deviation.

(45)

Subqueries in Expressions

A subquery is a SELECT statement enclosed in parentheses. The SELECT statement can contain one and only one select list item. When used as an expression a scalar subquery is allowed to return only zero or one value.

Syntax

<scalar_subquery_expression> ::= (<subquery>)

Within the SELECT list of the top level SELECT, or in the SET clause of an UPDATE statement, you can use a scalar subquery anywhere that you can use a column name. However, scalar_subquery cannot be used inside GROUP BY clause.

Example:

For example, the following statement returns the number of employees in each department, grouped by department name:

SELECT DepartmentName, COUNT(*), 'out of', (SELECT COUNT(*) FROM Employees)

FROM Departments AS D, Employees AS E WHERE D.DepartmentID = E.DepartmentID GROUP BY DepartmentName;

1.7 SQL Functions

Introduction

This chapter describes SQL Functions that are provided by SAP HANA Database. ● Data Type Conversion Functions

● DateTime Functions ● Fulltext Functions ● Number Functions ● String Functions ● Window Functions ● Miscellaneous Functions

1.7.1 Data Type Conversion Functions

Data type conversion functions are used to convert arguments from one data type to another, or to test whether a conversion is possible.

(46)

● CAST ● TO_ALPHANUM ● TO_BIGINT ● TO_BINARY ● TO_BLOB ● TO_CLOB ● TO_DATE ● TO_DATS ● TO_DECIMAL ● TO_DOUBLE ● TO_FIXEDCHAR ● TO_INT ● TO_INTEGER ● TO_NCLOB ● TO_NVARCHAR ● TO_REAL ● TO_SECONDDATE ● TO_SMALLDECIMAL ● TO_SMALLINT ● TO_TIME ● TO_TIMESTAMP ● TO_TINYINT ● TO_VARCHAR

1.7.1.1 CAST

Syntax

CAST (expression AS data_type)

Syntax Elements

(47)

Description

Returns the value of an expression converted to a supplied data type.

Example

SELECT CAST (7 AS VARCHAR) "cast" FROM DUMMY; cast 7

1.7.1.2 TO_ALPHANUM

Syntax

TO_ALPHANUM (value)

Description

Converts a given value to an ALPHANUM data type.

Example

SELECT TO_ALPHANUM ('10') "to alphanum" FROM DUMMY; to alphanum

(48)

1.7.1.3 TO_BIGINT

Syntax

TO_BIGINT (value)

Description

Converts a value to a BIGINT data type.

Example

SELECT TO_BIGINT ('10') "to bigint" FROM DUMMY; to bigint 10

1.7.1.4 TO_BINARY

Syntax

TO_BINARY (value)

Description

(49)

Example

SELECT TO_BINARY ('abc') "to binary" FROM DUMMY; to binary 616263

1.7.1.5 TO_BLOB

Syntax

TO_BLOB (value)

Description

Converts a value to a BLOB data type. The argument value must be a binary string.

Example

SELECT TO_BLOB (TO_BINARY('abcde')) "to blob" FROM DUMMY; to blob

abcde

1.7.1.6 TO_CLOB

Syntax

(50)

Description

Converts a value to a CLOB data type.

Example

SELECT TO_CLOB ('TO_CLOB converts the value to a CLOB data type') "to clob" FROM DUMMY;

to clob

TO_CLOB converts the value to a CLOB data type

1.7.1.7 TO_DATE

Syntax

TO_DATE (d [, format])

Description

Converts a date string d into a DATE data type. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats.

Example

SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY; to date

(51)

1.7.1.8 TO_DATS

Syntax

TO_DATS (d)

Description

Converts a date string d into an ABAP DATE string with format 'YYYYMMDD'.

Example

SELECT TO_DATS ('2010-01-12') "abap date" FROM DUMMY; abap date

20100112

1.7.1.9 TO_DECIMAL

Syntax

TO_DECIMAL (value [, precision, scale])

Description

Converts the value to a DECIMAL data type.

The precision is the total number of significant digits and can range from 1 to 34.

The scale is the number of digits from the decimal point to the least significant digit and can range from -6,111 to 6,176. This means that the scale specifies the range of the exponent in the decimal number from 10-6111_{to 10}6176_{. If}

the scale is not specified, it defaults to 0. Scale is positive when the number has significant digits to the right of the decimal point, and negative when the number has significant digits to the left of the decimal point.

(52)

When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In this case, precision and scale can vary within the range described above, 1~34 for precision and -6,111~6,176 for scale depending on the stored value.

Example

SELECT TO_DECIMAL(7654321.888888, 10, 3) "to decimal" FROM DUMMY to decimal 7654321.889

1.7.1.10 TO_DOUBLE

Syntax

TO_DOUBLE (value)

Description

Converts a value to a DOUBLE (double precision) data type.

Example

SELECT 3*TO_DOUBLE ('15.12') "to double" FROM DUMMY; to double

(53)

1.7.1.11 TO_FIXEDCHAR

Syntax

TO_FIXEDCHAR (str, n)

Description

Returns the first n characters of str

Example

SELECT TO_FIXEDCHAR ('Ant', 2) "to_fixedchar" FROM DUMMY; to_fixedchar An

1.7.1.12 TO_INT

Syntax

TO_INT (value)

Description

(54)

Example

SELECT TO_INT('10') "to int" FROM DUMMY; to int 10

1.7.1.13 TO_INTEGER

Syntax

TO_INTEGER (value)

Description

Converts the value to an INTEGER data type.

Example

SELECT TO_INTEGER ('10') "to int" FROM DUMMY; to int

10

1.7.1.14 TO_NCLOB

Syntax

(55)

Description

Converts a value to a NCLOB data type.

Example

SELECT TO_NCLOB ('TO_NCLOB converts the value to a NCLOB data type') "to nclob" FROM DUMMY;

to nclob

TO_NCLOB converts the value to a NCLOB data type

1.7.1.15 TO_NVARCHAR

Syntax

TO_NVARCHAR (value [,format])

Description

Converts the value to a NVARCHAR unicode character data type. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats.

Example

SELECT TO_NVARCHAR(TO_DATE('2009/12/31'), 'YY-MM-DD') "to nchar" FROM DUMMY; to nchar

(56)

1.7.1.16 TO_REAL

Syntax

TO_REAL (value)

Description

Converts a value to a REAL (single precision) data type.

Example

SELECT 3*TO_REAL ('15.12') "to real" FROM DUMMY; to real 45.36000061035156

1.7.1.17 TO_SECONDDATE

Syntax

TO_SECONDDATE (d [, format])

Description

Converts a date string d into a SECONDDATE data type. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats.

(57)

Example

SELECT TO_SECONDDATE ('2010-01-11 13:30:00', 'YYYY-MM-DD HH24:MI:SS') "to seconddate" FROM DUMMY;

to seconddate 2010-01-11 13:30:00.0

1.7.1.18 TO_SMALLDECIMAL

Syntax

TO_SMALLDECIMAL (value)

Description

Converts the value to a SMALLDECIMAL data type.

Example

SELECT TO_SMALLDECIMAL(7654321.89) "to smalldecimal" FROM DUMMY; to smalldecimal

7654321.89

1.7.1.19 TO_SMALLINT

Syntax

(58)

Description

Converts the value to a SMALLINT data type.

Example

SELECT TO_SMALLINT ('10') "to smallint" FROM DUMMY; to smallint 10

1.7.1.20 TO_TIME

Syntax

TO_TIME (t [, format])

Description

Converts a time string t into the TIME data type. If the format specifier is omitted, the conversion is performed using the time format model as explained in Time Formats.

Example

SELECT TO_TIME ('08:30 AM', 'HH:MI AM') "to time" FROM DUMMY; to time

(59)

1.7.1.21 TO_TIMESTAMP

Syntax

TO_TIMESTAMP (d [, format])

Description

Converts a date string d into the TIMESTAMP data type. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats.

Example

SELECT TO_TIMESTAMP ('2010-01-11 13:30:00', 'YYYY-MM-DD HH24:MI:SS') "to timestamp" FROM DUMMY;

to timestamp 2010-01-11 13:30:00.0

1.7.1.22 TO_TINYINT

Syntax

TO_TINYINT (value)

Description

(60)

Example

SELECT TO_TINYINT ('10') "to tinyint" FROM DUMMY; to tinyint

10

1.7.1.23 TO_VARCHAR

Syntax

TO_VARCHAR (value [, format])

Description

Converts a given value to a VARCHAR character data type. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats.

Example

SELECT TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY; to char 2009/12/31

1.7.2 DateTime Functions

● ADD_DAYS ● ADD_MONTHS ● ADD_SECONDS ● ADD_YEARS ● CURRENT_DATE ● CURRENT_TIME ● CURRENT_TIMESTAMP

SAP HANA Database System Views Reference En

PUBLIC

SAP HANA Platform SPS 06

Document Version: 1.0 - 26-06-2013

Table of Contents

1

SQL Reference Manual

1.1

Notation

Symbols used in BNF

BNF Lowest Terms Representations

1.2

Introduction

SQL

Supported Languages and Code Pages

Comment

Identifiers

Single Quotation Mark

Double Quotation Mark

SQL Reserved Words

1.3

Data Types

Classification of Data Types

Datetime Types

Numeric Types

Character String Types

Binary Types

Large Object (LOB) Types

Mapping between SQL Data Type and Column Store Data Type

Data Type Conversion

Typed Constant

1.4 Predicates

1.4.1

Comparison Predicates

Syntax

Syntax Elements

Description

1.4.2

Range Predicate

Syntax

Syntax Elements

Description

Note

1.4.3 In Predicate

Syntax

Syntax Elements

Description

1.4.4 Exists Predicate

Syntax

Syntax Elements

Description

1.4.5

LIKE Predicate

Syntax

Syntax Elements

Description

1.4.6 NULL Predicate

Syntax

Syntax Elements

Description

1.4.7

CONTAINS Predicate

1.5

Operators

Unary and Binary Operators

Operator Precedence

Arithmetic Operators

String Operators

Comparsion Operators

Logical Operators

Set Operators

1.6 Expressions

Syntax

Case Expressions

Function Expressions

Aggregate Expressions

Subqueries in Expressions

1.7

SQL Functions

Introduction