SAP HANA Appliance Software SPS 04
Target Audience
Consultants
Administrators
SAP Hardware Partner
Others
SAP Library document classification: PUBLIC
No part of this publication may be reproduced or transmitted in any form or for
any purpose without the express permission of SAP AG. The information
contained herein may be changed without prior notice.
Some software products marketed by SAP AG and its distributors contain
proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio
are registered trademarks of Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System
p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise,
PowerVM, Power Architecture, Power Systems, POWER7, POWER6+,
POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System
Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF,
Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter
Planet are trademarks or registered trademarks of IBM Corporation.
Linux is the registered trademark of Linus Torvalds in the United States and other
countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or
registered trademarks of Adobe Systems Incorporated in the United States and
other countries.
Oracle and Java are registered trademarks of Oracle and its affiliates.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and
MultiWin are trademarks or registered trademarks of Citrix Systems Inc.
HTML, XML, XHTML, and W3C are trademarks or registered trademarks of
W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch,
Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered
trademarks of Apple Inc.
IOS is a registered trademark of Cisco Systems Inc.
RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl,
BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook,
and BlackBerry App World are trademarks or registered trademarks of Research
Google App Engine, Google Apps, Google Checkout, Google Data API, Google
Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store,
Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube,
Dalvik and Android are trademarks or registered trademarks of Google Inc.
INTERMEC is a registered trademark of Intermec Technologies Corporation.
Wi-Fi is a registered trademark of Wi-Fi Alliance.
Bluetooth is a registered trademark of Bluetooth SIG Inc.
Motorola is a registered trademark of Motorola Trademark Holdings LLC.
Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products
and services mentioned herein as well as their respective logos are trademarks or
registered trademarks of SAP AG in Germany and other countries.
Business Objects and the Business Objects logo, BusinessObjects, Crystal
Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business
Objects products and services mentioned herein as well as their respective logos
are trademarks or registered trademarks of Business Objects Software Ltd.
Business Objects is an SAP company.
Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other
Sybase products and services mentioned herein as well as their respective logos
are trademarks or registered trademarks of Sybase Inc. Sybase is an SAP
company.
Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are registered
trademarks of Crossgate AG in Germany and other countries. Crossgate is an
SAP company.
All other product and service names mentioned are the trademarks of their
respective companies. Data contained in this document serves informational
purposes only. National product specifications may vary.
These materials are subject to change without notice. These materials are
provided by SAP AG and its affiliated companies ("SAP Group") for informational
purposes only, without representation or warranty of any kind, and SAP Group
shall not be liable for errors or omissions with respect to the materials. The only
warranties for SAP Group products and services are those that are set forth in
the express warranty statements accompanying such products and services, if
any. Nothing herein should be construed as constituting an additional warranty.
4
8
9
10
11
11 11 11 11 11 11 1213
13 13 13 14 14 14 15 15 16 17 17 18 18 2022
22 22 22 22 22 22 2325
25 25 25 26 26 26 2628
28 28 28 2830
30 30 30 30 30 31 31 31 31 32 32Table of Content
Table of Content
SAP HANA Database - SQL Reference Manual
SQL Reference Ma nua l
Nota ti on
Introducti on
SQL
Supported Languages and Code Pages Comment
Identifiers
Single Quotation Mark Double Quotation Mark SQL Reserved Words
Da ta Types
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
Predi ca tes
Comparison Predicates Range Predicate In Predicate Exists Predicate LIKE Predicate NULL Predicate CONTAINS PredicateOpera tors
Unary and Binary Operators Operator Precedence Arithmetic Operators String Operators Comparsion Operators Logical Operators Set Operators
Expres s i ons
Case Expressions Function Expressions Aggregate Expressions Subqueries in ExpressionsSQL Functi ons
IntroductionData Type Conversion Functions CAST TO_ALPHANUM TO_BIGINT TO_BINARY TO_BLOB TO_CHAR TO_CLOB TO_DATE TO_DATS
32 33 33 33 33 34 34 34 34 35 35 35 35 36 36 36 36 37 37 37 37 38 38 38 38 39 39 39 39 40 40 40 40 41 41 41 42 42 42 42 43 43 43 43 44 44 44 44 44 45 45 45 46 46 46 46 47 47 47 47 48 48 48 48 49 49 49 50 50 50 50 51 51 51 51 52 TO_DECIMAL TO_DOUBLE TO_INT TO_INTEGER TO_NCHAR TO_NCLOB TO_NVARCHAR TO_REAL TO_SECONDDATE TO_SMALLDECIMAL TO_SMALLINT TO_TIME TO_TIMESTAMP TO_TINYINT TO_VARCHAR DateTime Functions ADD_DAYS ADD_MONTHS ADD_SECONDS ADD_YEARS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_UTCDATE CURRENT_UTCTIME CURRENT_UTCTIMESTAMP DAYNAME DAYOFMONTH DAYOFYEAR DAYS_BETWEEN EXTRACT HOUR ISOWEEK LAST_DAY LOCALTOUTC MINUTE MONTH MONTHNAME NEXT_DAY NOW QUARTER SECOND SECONDS_BETWEEN UTCTOLOCAL WEEK WEEKDAY YEAR Number Functions ABS ACOS ASIN ATAN ATAN2 BINTOHEX BITAND CEIL COS COSH COT EXP FLOOR GREATEST HEXTOBIN LEAST LN LOG MOD POWER ROUND SIGN SIN SINH SQRT TAN TANH UMINUS
52 52 52 53 53 53 53 54 54 54 55 55 55 55 56 56 56 57 57 57 58 58 58 59 59 59 59 59 60 61 61 62 62 63 63
64
64 64 65 65 66 67 70 72 73 73 74 74 75 76 80 82 82 83 83 84 84 84 85 85 86 86 86 87 87 87 88 88 89 89 89 92 93 93 93 String Functions ASCII CHAR CONCAT LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM NCHAR REPLACE RIGHT RPAD RTRIM SUBSTR_AFTER SUBSTR_BEFORE SUBSTRING TRIM UCASE UNICODE UPPER Miscellaneous Functions COALESCE CURRENT_CONNECTION CURRENT_SCHEMA CURRENT_USER GROUPING_ID IFNULL MAP NULLIF SESSION_CONTEXT SESSION_USER SYSUUIDSQL Sta tements
Schema Definition and Manipulation Statements ALTER AUDIT POLICY
ALTER FULLTEXT INDEX ALTER INDEX ALTER SEQUENCE ALTER TABLE CREATE AUDIT POLICY CREATE CALCULATION SCENARIO CREATE FULLTEXT INDEX CREATE INDEX CREATE SCHEMA CREATE SEQUENCE CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE VIEW DROP AUDIT POLICY DROP CALCULATION SCENARIO DROP FULLTEXT INDEX DROP INDEX DROP SCHEMA DROP SEQUENCE DROP SYNONYM DROP TABLE DROP TRIGGER DROP TYPE DROP VIEW RENAME COLUMN RENAME INDEX RENAME TABLE ALTER TABLE ALTER TYPE TRUNCATE TABLE
Data Manipulation Statements DELETE EXPLAIN PLAN INSERT LOAD MERGE DELTA REPLACE | UPSERT
94 101 101 102 102 102 103 104 104 104 105 105 105 105 106 106 106 107 107 107 107 108 108 108 108 108 109 109 109 109 110 110 110 111 111 111 111 112 112 112 113 115 115 116 117 117 118 118 123 124 124 125 126 SELECT UNLOAD UPDATE
System Management Statements SET SYSTEM LICENSE
ALTER SYSTEM ALTER CONFIGURATION ALTER SYSTEM ALTER SESSION SET ALTER SYSTEM CANCEL [WORK IN] SESSION ALTER SYSTEM CLEAR SQL PLAN CACHE ALTER SYSTEM CLEAR TRACES
ALTER SYSTEM DELETE ALL HANDLED EVENTS ALTER SYSTEM DELETE HANDLED EVENT ALTER SYSTEM DISCONNECT SESSION ALTER SYSTEM LOGGING
ALTER SYSTEM RECLAIM DATAVOLUME ALTER SYSTEM RECLAIM LOG ALTER SYSTEM RECLAIM VERSION SPACE ALTER SYSTEM RECONFIGURE SERVICE ALTER SYSTEM REMOVE TRACES ALTER SYSTEM RESET MONITORING VIEW ALTER SYSTEM SAVE PERFTRACE ALTER SYSTEM SAVEPOINT ALTER SYSTEM SET EVENT HANDLED ALTER SYSTEM START PERFTRACE ALTER SYSTEM STOP PERFTRACE ALTER SYSTEM STOP SERVICE UNSET SYSTEM LICENSE ALL Session Management Statements
CONNECT SET HISTORY SESSION SET SCHEMA SET [SESSION] UNSET [SESSION]
Transaction Management Statements COMMIT
LOCK TABLE ROLLBACK SET TRANSACTION Access Control Statements
ALTER SAML PROVIDER ALTER USER CREATE ROLE CREATE SAML PROVIDER CREATE USER DROP ROLE DROP SAML PROVIDER DROP USER GRANT REVOKE
Data Import Export Statements EXPORT
IMPORT IMPORT FROM
SAP HANA Database - SQL Reference Manual
This guide describes SQL language supported by SAP HANA DB.Notation
This reference use BNF (Backus Naur Form) which is the notation technique used to define programming languages, to describe SQL. BNF describes the syntax of a grammar using a set of production rules using a set of symbols.
Symbols used in BNF Symbol Description
< > Angl e bra ckets a re us ed to s urround the na me of a s ynta cti c el ement (BNF nontermi na l ) of the SQL l a ngua ge.
::= The defi ni ti on opera tor i s us ed to provi de defi ni ti ons of the el ement a ppea red on the l eft s i de of the opera tor i n a producti on rul e.
[ ] Squa re bra ckets a re us ed to i ndi ca te opti ona l el ements i n a formul a . Opti ona l el ements ma y be s peci fi ed or omi tted.
{ } Bra ces group el ements i n a formul a . Repeti ti ve el ements (zero or more el ements ) ca n be s peci fi ed wi thi n bra ce s ymbol s .
| The a l terna ti ve opera tor i ndi ca tes tha t the porti on of the formul a fol l owi ng the ba r i s a n a l terna ti ve to the porti on precedi ng the ba r.
...
The el l i ps i s i ndi ca tes tha t the el ement ma y be repea ted a ny number of ti mes . If el l i ps i s a ppea rs a fter grouped el ements s peci fyi ng tha t the grouped el ements encl os ed wi th bra ces a re repea ted. If el l i ps i s a ppea rs a fter a s i ngl e el ement, onl y tha t el ement i s repea ted.
!! Introduces norma l Engl i s h text. Thi s i s us ed when the defi ni ti on of a s ynta cti c el ement i s not expres s ed i n BNF.
Introduction
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
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:
Double hyphens "--". Everything after the double hyphen until the end of a line is considered by the SQL parser to be a comment
"/*" 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.
Identifiers
Syntax:<identifier> ::= <simple_identifier> | <double_quotes><special_identifier><double_qu otes>
<simple_identifier> ::= <letter> [{<letter_or_digit>|<underscore>}, ...] <double_quotes> ::= "
<special_identifier> ::= any character
<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 <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
<letter_or_digit> ::= <letter> | <digit> <underscore> ::= _
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.
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. 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_UTCTIMESTAMP 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
SET SQL START SYSDATE
SYSTIME SYSTIMESTAMP SYSUUID TOP
TRAILING UNION USING UTCDATE
UTCTIME UTCTIMESTAMP VALUES WHEN
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
In the SAP HANA Database each data type can be classified by its characteristic as follows:
Table 2: Classification of data types Classification Data Type
Da teti me types DATE, TIME, SECONDDATE, TIMESTAMP
Numeri c types TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE Cha ra cter s tri ng types VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT
Bi na ry types VARBINARY
La rge Object types BLOB, CLOB, NCLOB, TEXT
Datetime Types
DATEThe 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.
Table 4: Supported formats for Date
Format Description Examples
YYYY-MM-DD Defa ul t forma t INSERT INTO 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 yea r ha s l es s tha n four di gi ts , month ha s l es s tha n two di gi ts , or da y ha s l es s tha n two di gi ts , then va l ues wi l l be pa dded by one or more zeros . For exa mpl e, a two di gi t yea r 45 wi l l be s a ved a s yea r 0045, a one di gi t month 9 wi l l be s a ved a s 09, a nd a one di gi t da y 2 wi l l be s a ved a s 02.
INSERT INTO TBL VALUES ('1957-06-13'); INSERT INTO TBL VALUES ('1957/06/13'); INSERT INTO TBL VALUES ('1957/06-13'); INSERT INTO TBL VALUES ('1957-06/13');
YYYYMMDD ABAP Da ta Type, DATS forma t. INSERT INTO TBL VALUES ('19570613');
MON Abbrevi a ted na me of month. (JAN. ~ DEC.)
INSERT INTO TBL VALUES (TO_DATE('2040-Ja n-10', 'YYYY-MON-DD'));
INSERT INTO TBL VALUES (TO_DATE('Ja n-10', 'MON-DD'));
MONTH Na me of month. (JANUARY - DECEMBER).
INSERT INTO TBL VALUES (TO_DATE('2040-Ja nua ry-10', 'YYYY-MONTH-DD')); INSERT INTO TBL VALUES
(TO_DATE('Ja nua ry-10', 'MONTH-DD'));
RM Roma n numera l month (I-XII; JAN = I).
INSERT INTO TBL VALUES (TO_DATE('2040-I-10', 'YYYY-RM-DD'));
INSERT INTO TBL VALUES (TO_DATE('I-10', 'RM-DD'));
DDD Da y of yea r (1-366).
INSERT INTO TBL VALUES (TO_DATE('204', 'DDD'));
INSERT INTO TBL VALUES (TO_DATE('2001-204','YYYY-DDD'));
Time Formats
Table 5: Supported formats for Time
Format Description Examples
HH24:MI:SS Defa ul t forma t
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 di gi t hour, mi nute, s econd i s s peci fi ed, then 0 wi l l be i ns erted i nto the va l ue. For exa mpl e, 9:9:9 wi l l be s a ved a s 09:09:09.
HH12 i ndi ca tes 12 hour cl ock a nd HH24 i ndi ca tes 24 hour cl ock.
AM or PM ca n be s peci fi ed a s a s uffi x to i ndi ca te the ti me va l ue i s before or a fter noon.
INSERT INTO TBL VALUES ('23:59:59'); INSERT INTO TBL VALUES ('3:47:39 AM'); INSERT INTO TBL VALUES ('9:9:9 AM'); INSERT INTO TBL VALUES
(TO_TIME('11:59:59','HH12:MI:SS');
SSSSS Seconds pa s t mi dni ght (0-86399). INSERT INTO TBL VALUES (TO_TIME('12345', 'SSSSS'));
Timestamp Formats
Table 6: Supported formats for Timestamp
Format Description Examples
YYYY-MM-DD
HH24:MI:SS.FF7 Defa ul t forma t
FF [1..7]
Fra cti ona l s econds ha s the ra nge 1 to 7 a fter the FF pa ra meter to s peci fy the number of di gi ts i n the fra cti ona l s econd porti on of the da te ti me va l ue returned. If a di gi t i s not s peci fi ed, the defa ul t va l ue i s us ed.
INSERT INTO TBL VALUES (TO_TIMESTAMP('2011-05-11 12:59.999','YYYY-MM-DD HH:SS.FF3'));
Table 7: Additional formats for Datetime
Format Description Example
D Da y of week (1-7). TO_CHAR(CURRENT_TIMESTAMP,'D')
DAY Na me of da y (MONDAY - SUNDAY). TO_CHAR(CURRENT_TIMESTAMP,'DAY') DY Abbrevi a ted na me of da y (MON - SUN). TO_CHAR(CURRENT_TIMESTAMP,'DY') MON Abbrevi a ted month na me (JAN - DEC) TO_CHAR(CURRENT_TIMESTAMP,'MON') MONTH Ful l month na me (JANUARY - DECEMBER) TO_CHAR(CURRENT_TIMESTAMP,'MONTH') RM Roma n numera l month (I - XII; I i s for
Ja nua ry) TO_CHAR(CURRENT_TIMESTAMP,'RM')
Q Qua rter of yea r (1, 2, 3, 4) TO_CHAR(CURRENT_TIMESTAMP,'Q')
W Week of month (1-5). TO_CHAR(CURRENT_TIMESTAMP,'W')
WW Week of yea r (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 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_CHAR TO_DATE TO_DATS TO_NCHAR TO_TIME TO_TIMESTAMP UTCTOLOCAL WEEK WEEKDAY YEAR
Numeric Types
TINYINTThe TINYINT data type stores an 8-bit unsigned integer. The minimum value is 0 and the maximum value is 255 for TINYINT.
SMALLINT
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 64bit 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)
The DECIMAL(p, s) data type specifies a fixed-point decimal number with precision p and scale s. 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, which means scale specifies the range of the exponent in the decimal number from 10-6111 to 106176. If 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.
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.1416, 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.
FLOAT(n)
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 64-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
textsearch-features and stringsearch-textsearch-features. This is not a standalone sql-type. Selecting a SHORTTEXT(n)-column yields a SHORTTEXT(n)-column of type NVARCHAR(n).
<shorttext_type> ::= SHORTTEXT '(' int_const ')' <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.
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.
LOB columns cannot be used in set operations such as EXCEPT. UNION ALL is an exception. 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 Integer Types TINYINT, SMALLINT, INT CS_INT
BIGINT CS_FIXED(18,0)
Approxi ma te Types REAL CS_FLOAT
DOUBLE CS_DOUBLE
FLOAT CS_DOUBLE
FLOAT(p) CS_FLOAT, CS_DOUBLE Deci ma l Types DECIMAL CS_DECIMAL_FLOAT
DECIMAL(p,s ) CS_FIXED(p-s ,s ) SMALLDECIMAL CS_SDFLOAT
Cha ra cter Types VARCHAR CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT,CS_DATE,CS_TIME NVARCHAR CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT
CLOB, NCLOB CS_STRING
ALPHANUM CS_ALPHANUM
Bi na ry Types BLOB CS_RAW
VARBINARY CS_RAW
Da te/Ti me Types DATE CS_DAYDATE,CS_DATE
TIME CS_SECONDTIME,CS_TIME
TIMESTAMP CS_LONGDATE,CS_DATE,CS_SECONDDATE
SECONDDATE CS_SECONDDATE
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 8: Implicit Type conversion Examples
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 beca us e there i s no convers i on a va i l a bl e between DATE a nd 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.
Table 9a: Data type conversion table Target/ Source tinyint - OK OK OK OK OK OK OK OK OK OK smallint CHK - OK OK OK OK OK OK OK OK OK integer CHK CHK - OK OK OK OK OK OK OK OK bigint CHK CHK CHK - OK CHK CHK CHK OK OK OK decimal CHK CHK CHK CHK - CHK CHK CHK OK OK OK decimal(p,s) CHK CHK CHK CHK CHK CHK CHK CHK CHK CHK OK smalldecimal CHK CHK CHK CHK OK CHK - CHK CHK OK OK real CHK CHK CHK CHK OK CHK CHK - OK OK OK double CHK CHK CHK CHK CHK CHK CHK CHK - OK OK varchar CHK CHK CHK CHK CHK CHK CHK CHK CHK - OK nvarchar CHK CHK CHK CHK CHK CHK CHK CHK CHK CHK
-Table 9b: Data type conversion table Target/
Source time date seconddate timestamp varchar nvarchar
time - - - - OK OK date - - OK OK OK OK seconddate ti me da te - ti mes ta mp OK OK timestamp ti me da te s econdda te - OK OK varchar CHK CHK CHK CHK - OK nvarchar CHK CHK CHK CHK CHK
-Table 9c: Data type conversion table 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. ti n yi n t sm al lin t in te ge r b ig in t d ec im al d ec im al (p ,s ) sm al ld e ci m al re al d o u b le va rc h ar n va rc h ar
Highest TIMESTAMP SECONDDATE DATE TIME DOUBLE REAL DECIMAL SMALLDECIMAL BIGINT INTEGER SMALLINT TINYINT NCLOB NVARCHAR CLOB VARCHAR BLOB Lowest VARBINARY
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 st ring" 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" FR OM DUMMY;
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
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
Two values are compared using comparison predicates and the comparison returns true, false, or unknown. Syntax:
<comparison_predicate> ::=
<expression> { = | != | <> | > | < | >= | <= } [ ANY | SOME| ALL ] { <expression_li st> | <subquery> }
<expression_list> ::= <expression>, ...
Expressions can be a simple expression such as a character, date, or number. An expression can also be a scalar subquery.
ANY, SOME - When ANY or SOME are specified, the comparison returns true if at least one value returned by the subquery or expression_list is true. ALL - When ALL is specified, the comparison returns true if the comparison of all values returned by the subquery or expression_list is true.
Range Predicate
A value is compared with a list of values within the provided range. Syntax:
<range_predicate> ::= <expression1> [NOT] BETWEEN <expression2> AND <expression3> BETWEEN ... AND ... - When a range predicate is used, it returns true if expression1 is within the range specified by expression2 and expression3. True will only be returned if expression2 has a lesser value than expression3.
In Predicate
A value is compared with a specified set of values. True will be returned if the value of expression1 is found in the expression_list (or subquery).
Syntax:
<in_predicate> ::= <expression> [NOT] IN { <expression_list> | <subquery> }
Exists Predicate
Returns true if the subquery returns a result set that is not empty and returns false if the subquery returns an empty result set.
Syntax:
<exists_predicate> ::= [NOT] EXISTS ( <subquery> )
LIKE Predicate
The LIKE predicate is used for string comparisons. Expression1 is tested for a pattern contained in expression2. Wildcard characters ( % ) and ( _ ) may 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 in the LIKE predicate, an escape character must be used. Using the optional argument, ESCAPE expression3, you can specify the escape character that will be used allowing the underscore (_) or percentage sign (%) to be matched.
Syntax:
<like_predicate> ::= <expression1> [NOT] LIKE <expression2> [ESCAPE <expression3>]
NULL Predicate
When the IS NULL predicate is specified, a value can be compared with NULL. IS NULL returns true if the expression value is NULL. If the IS NOT NULL predicate is specified, it returns true if a value is not NULL. Syntax:
<null_predicate> ::= <expression> IS [NOT] NULL
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> ',' <se arch_specifier> ')'
<contains_columns> ::= '*' | <column_name> | '(' <columnlist> ')'
<search_string> ::= <string_const>
<search_specifier> ::= <search_type> <opt_search_specifier2_list> | <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)
float_const
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 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:
The freestyle-search is a search mulitple columns.
select * from T where CONTAINS( (column1,column2,column3), 'cats OR dogz', FUZZY(0. 7))
select * from T where CONTAINS( (column1,column2,column3), 'cats OR dogz', FUZZY(0. 7))
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 10. Unary and binary operators
Operator Operation Format Description
Una ry A una ry opera tor a ppl i es to one opera nd or a
s i ngl e va l ue expres s i on. opera tor opera nd
una ry pl us opera tor(+) una ry nega ti on opera tor(-) l ogi ca l nega ti on(NOT)
Bi na ry Bi na ry A bi na ry opera tor a ppl i es to two opera nds or two va l ue expres s i ons .
opera nd1 opera tor opera nd2
mul ti pl i ca ti ve opera tors ( *, / ) a ddi ti ve opera tors ( +,- )
compa ri s on opera tors ( =,!=,<,>,<=,>=) l ogi ca l opera tors ( 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 11. SQL operator precedence
Precedence Operator Operation
Hi ghes t () pa renthes es
+, - una ry pos i ti ve a nd nega ti ve opera ti on *, / mul ti pl i ca ti on, di vi s i on
+, - a ddi ti on, s ubtra cti on
|| conca tena ti on
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN compa rs i on
NOT l ogi ca l nega ti on
AND conjuncti on
Lowes t OR di s juncti on
Arithmetic Operators
You use arithmetic operators to perform mathematical operations such as adding, subtracting, multiplying, dividing and negation of numeric values.
Table 12. Arithmetic operators
Operator Description
-<expres s i on> Nega ti on. If the expres s i on i s the NULL va l ue, the res ul t i s NULL. <expres s i on> +
<expres s i on> Addi ti on. If ei ther expres s i on i s the NULL va l ue, the res ul t i s NULL. <expres s i on>
-<expres s i on> Subtra cti on. If ei ther expres s i on i s the NULL va l ue, the res ul t i s NULL. <expres s i on> *
<expres s i on> Mul ti pl i ca ti on. If ei ther expres s i on i s NULL, the res ul t i s NULL. <expres s i on> /
<expres s i on>
Di vi s i on. If ei ther expres s i on i s NULL, or i f the s econd expres s i on i s 0, a n error i s returned.
String Operators
A concatenation operator combines two items such as strings, expressions or constants into one.
Table 13. Concatenation operators
Operator Description
<expres s i on> || <expres s i on> Stri ng conca tena ti on (two verti ca l ba rs ). If ei ther s tri ng i s NULL, i t returns NULL.
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 14. Comparison operators
Operator Description Example
= Equa l to SELECT * FROM s tudents WHERE i d = 25; > Grea ter tha n SELECT * FROM s tudents WHERE i d > 25; < Les s tha n SELECT * FROM s tudents WHERE i d < 25; >= Grea ter tha n or equa l to SELECT * FROM s tudents WHERE i d >= 25; <= Les s tha n or equa l to SELECT * FROM s tudents WHERE i d <= 25; !=, <> Not equa l SELECT * FROM s tudents WHERE i d != 25; SELECT * FROM s tudents WHERE i d <> 25;
Logical Operators
Search conditions can be combined using AND or OR operators. You can also negate them using the NOT operator.
Table 15. Logical operators
Operator Syntax Description
AND
WHERE condi ti on1 AND condi ti on2
When us i ng AND, the combi ned condi ti on i s TRUE i f both condi ti ons a re TRUE, FALSE i f ei ther condi ti on i s FALSE, a nd UNKNOWN otherwi s e.
OR
WHERE condi ti on1 OR condi ti on2
When us i ng OR, the combi ned condi ti on i s TRUE i f ei ther condi ti on i s TRUE, FALSE i f both condi ti ons a re FALSE, a nd UNKNOWN otherwi s e.
NOT WHERE NOT
condi ti on
The NOT opera tor i s pl a ced before a condi ti on to nega te the condi ti on. The NOT condi ti on i s TRUE i f condi ti on i s FALSE, FALSE i f condi ti on i s TRUE, a nd UNKNOWN i f condi ti on i s UNKNOWN.
Set Operators
Table 16. Set operators Operator Returned Value
UNION Combi nes the res ul ts of two or more s el ect s ta tements or query expres s i ons UNION
ALL
Combi nes the res ul ts of two or more s el ect s ta tements or query expres s i ons , i ncl udi ng a l l dupl i ca te rows .
INTERSECT Combi nes the res ul ts of two or more s el ect s ta tements or query expres s i ons , a nd returns a l l common rows .
Expressions
An expression is a clause that can be evaluated to return values. Syntax: <expression> ::= <case_expression> | <function_expression> | <aggregate_expression> | (<expression> ) | ( <subquery> ) | - <expression>
| <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> ::= CASE <expression>
WHEN <expression> THEN <expression>, ... [ ELSE <expression>]
{ END | END CASE }
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>, ... )
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 , rega rdl es s of the va l ue of thos e rows a nd i ncl udi ng dupl i ca te va l ues . COUNT(<expres s i on>) returns the number of non-NULL va l ues for tha t expres s i on returned by the query.
MIN Returns the mi ni mum va l ue of expres s i on. MAX Returns the ma xi mum va l ue of expres s i on. SUM Returns the s um of expres s i on.
AVG Returns the a ri thmeti ca l mea n of expres s i on.
STDDEV Returns the s ta nda rd devi a ti on of gi ven expres s i on a s the s qua re root of VARIANCE functi on. VAR Returns the va ri a nce of expres s i on a s the s qua re of s ta nda rd devi a ti on.
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;
SQL Functions
Introduction
This chapter describes SQL Functions that are provided by SAP HANA Database.
Data Type Conversion Functions DateTime Functions
Number Functions String Functions Miscellaneous Functions
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.
CAST Syntax
CAST (expression AS data_type) Syntax Elements
expression - The expression to be converted. data type - The target data type. TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | ALPHANUM | VARCHAR | NVARCHAR | DAYDATE | DATE | TIME | SECONDDATE | TIMESTAMP
Description
Returns the value of an expression converted to a supplied data type. Example
SELECT CAST (7 AS VARCHAR) "cast" FROM DUMMY; cast 7 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 10 TO_BIGINT Syntax TO_BIGINT (value) Description
Example
SELECT TO_BIGINT ('10') "to bigint" FROM DUMMY; to bigint 10 TO_BINARY Syntax TO_BINARY (value) Description
Converts a value to a BINARY data type. Example
SELECT TO_BINARY ('abc') "to binary" FROM DUMMY; to binary 616263 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 TO_CHAR Syntax
TO_CHAR (value [, format]) Description
Converts a given value to a CHAR 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_CHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY; to char 2009/12/31 TO_CLOB Syntax TO_CLOB (value) 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 DU MMY;
to clob
TO_CLOB converts the value to a CLOB data type 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 2010-01-12 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 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 106176. 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.
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 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 45.36 TO_INT Syntax TO_INT (value) Description
Converts the value to an INTEGER data type. Example
SELECT TO_INT('10') "to int" FROM DUMMY; to int 10 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 TO_NCHAR Syntax
TO_NCHAR (value [, format]) Description
is performed using the date format model as explained in Date Formats. Example
SELECT TO_NCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to nchar" FROM DUMMY; to nchar 2009/12/31 TO_NCLOB Syntax TO_NCLOB (value) 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" FRO M DUMMY;
to nclob
TO_NCLOB converts the value to a NCLOB data type 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 09-12-31 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 TO_SECONDDATE Syntax
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.
Example
SELECT TO_SECONDDATE ('2010-01-11 13:30:00', 'YYYY-MM-DD HH24:MI:SS') "to seconddat e" FROM DUMMY;
to seconddate 2010-01-11 13:30:00.0 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 TO_SMALLINT Syntax TO_SMALLINT (value) Description
Converts the value to a SMALLINT data type. Example
SELECT TO_SMALLINT ('10') "to smallint" FROM DUMMY; to smallint 10 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
08:30:00 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 TO_TINYINT Syntax TO_TINYINT (value) Description
Converts the value to a TINYINT data type. Example
SELECT TO_TINYINT ('10') "to tinyint" FROM DUMMY; to tinyint
10 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
DateTime Functions
ADD_DAYS Syntax ADD_DAYS (d, n) DescriptionComputes the date d plus n days. Example
SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) "add days" FROM DUMMY; add days 2010-01-04 ADD_MONTHS Syntax ADD_MONTHS (d, n) Description
Computes the date d plus n months. Example
SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUMMY ; add months 2010-01-05 ADD_SECONDS Syntax ADD_SECONDS (t, n) Description
Computes the time t plus n seconds. Example
SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 60*30) "add seconds" FROM DUMMY; add seconds 2012-01-02 00:00:45.0 ADD_YEARS Syntax ADD_YEARS (d, n) Description
Computes the date d plus n years. Example
SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY; add years 2010-12-05 CURRENT_DATE Syntax CURRENT_DATE Description
Example
SELECT CURRENT_DATE "current date" FROM DUMMY; current date 2010-01-11 CURRENT_TIME Syntax CURRENT_TIME Description
Returns the current local system time. Example
SELECT CURRENT_TIME "current time" FROM DUMMY; current time 17:37:37 CURRENT_TIMESTAMP Syntax CURRENT_TIMESTAMP Description
Returns the current local system timestamp information. Example
SELECT CURRENT_TIMESTAMP "current timestamp" FROM DUMMY; current timestamp 2010-01-11 17:38:48.802 CURRENT_UTCDATE Syntax CURRENT_UTCDATE Description
Returns the current UTC date. The UTC stands for Coordinated Universal Time, also known as Greenwich Mean Time (GMT).
Example
SELECT CURRENT_UTCDATE "Coordinated Universal Date" FROM DUMMY; Coordinated Universal Time
2010-01-11 CURRENT_UTCTIME Syntax
CURRENT_UTCTIME Description
Returns the current UTC time. Example
SELECT CURRENT_UTCTIME "Coordinated Universal Time" FROM DUMMY; Coordinated Universal Time
08:41:19
CURRENT_UTCTIMESTAMP Syntax
CURRENT_UTCTIMESTAMP Description
Returns the current UTC timestamp. Example
SELECT CURRENT_UTCTIMESTAMP "Coordinated Universal Timestamp" FROM DUMMY; Coordinated Universal Timestamp
2010-01-11 08:41:42.484 DAYNAME
Syntax DAYNAME (d) Description
Returns the weekday in English for date d. Example
SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY; dayname MONDAY DAYOFMONTH Syntax DAYOFMONTH (d) Description
Returns an integer the day of the month for date d. Example
SELECT DAYOFMONTH ('2011-05-30') "dayofmonth" FROM DUMMY; dayofmonth
30 DAYOFYEAR Syntax
Description
Returns an integer representation of the day of the year for date d. Example
SELECT DAYOFYEAR ('2011-05-30') "dayofyear" FROM DUMMY; dayofyear 150 DAYS_BETWEEN Syntax DAYS_BETWEEN (d1, d2) Description
Computes the number of days between d1 and d2. Example
SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'Y YYY-MM-DD')) "days between" FROM DUMMY;
days between 31
EXTRACT Syntax
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d) Description
Finds and returns the value of a specified datetime field from date d. Example
SELECT EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) "extract" FROM DUM MY; extract 2010 HOUR Syntax HOUR (t) Description
Returns an integer representation of the hour for time t. Example
SELECT HOUR ('12:34:56') "hour" FROM DUMMY; hour
12 ISOWEEK Syntax
ISOWEEK (d) Description
Returns the ISO year and week numbers of date d. The week number is prefixed by the letter W. Please also see WEEK
Example
SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY; isoweek 2011-W22 LAST_DAY Syntax LAST_DAY (d) Description
Returns the date of the last day of the month that contains the date d. Example
SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY; last day 2010-01-31 LOCALTOUTC Syntax LOCALTOUTC (t, timezone) Description
Convert the local time t from a timezone to the UTC(GMT) time Example
SELECT LOCALTOUTC (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), ' EST') "localtoutc" FROM DUMMY;
localtoutc 2012-01-01 06:00:00.0 MINUTE Syntax MINUTE (t) Description
Returns an integer representation of the minute for time t. Example
SELECT MINUTE ('12:34:56') "minute" FROM DUMMY; minute
MONTH Syntax
MONTH(d) Description
Returns the number of the month from date d. Example
SELECT MONTH ('2011-05-30') "month" FROM DUMMY; month 5 MONTHNAME Syntax MONTHNAME(d) Description
Returns the name of the month in English for date d. Example
SELECT MONTHNAME ('2011-05-30') "monthname" FROM DUMMY; monthname MAY NEXT_DAY Syntax NEXT_DAY (d) Description
Returns the date of the next day after date d. Example
SELECT NEXT_DAY (TO_DATE ('2009-12-31', 'YYYY-MM-DD')) "next day" FROM DUMMY; next day 2010-01-01 NOW Syntax NOW () Description
Returns the current timestamp. Example
SELECT NOW () "now" FROM DUMMY; now
2010-01-01 16:34:19.894 QUARTER
Syntax
QUARTER (d, [, start_month ]) Description
Returns the numerical year quarter of date d. The first quarter starts in the month specified by start_month. If start_month is not specified the first quarter is assumed to begin in January.
Example
SELECT QUARTER (TO_DATE('2012-01-01', 'YYYY-MM-DD'), 2) "quarter" FROM DUMMY; quarter 2011-Q4 SECOND Syntax SECOND (t) Description
Returns an integer representation of the second for time t. Example
SELECT SECOND ('12:34:56') "second" FROM DUMMY; second 56 SECONDS_BETWEEN Syntax SECONDS_BETWEEN (d1, d2) Description
Computes the number of seconds between date arguments d1 and d2, which is semantically equal to d2 - d1. Example
SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') "seconds between" FROM DUMMY; Seconds between 2678400 UTCTOLOCAL Syntax UTCTOLOCAL (t, timezone) Description
Convert the UTC(GMT) time t to the local time in a timezone Example
SELECT UTCTOLOCAL (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), ' EST') "utctolocal" FROM DUMMY;
utctolocal 2011-12-31 20:00:00.0 WEEK Syntax WEEK (d) Description
Returns the week number of date d. Please also see ISOWEEK
Example
SELECT WEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "week" FROM DUMMY; week 23 WEEKDAY Syntax WEEKDAY (d) Description
Returns an integer representation of the day of the week for date d. The return value ranges from 0 to 6, representing Monday(0) through to Sunday(6).
Example
SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM DUMMY; week day 4 YEAR Syntax YEAR (d) Description
Returns the year number of date d. Example
SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY; year
2011
Number Functions
Number functions take numeric values, or strings with numeric characters, as inputs and return numeric values. When strings with numeric characters are provided as inputs implicit conversion from a string to a number is performed automatically before results are computed.
Syntax ABS (n) Description
Returns the absolute value of the numeric argument n. Example
SELECT ABS (-1) "absolute" FROM DUMMY; absolute 1 ACOS Syntax ACOS (n) Description
Returns the arc-cosine, in radians, of the numeric argument n between -1 and 1. Example
SELECT ACOS (0.5) "acos" FROM DUMMY; acos 1.0471975511965979 ASIN Syntax ASIN (n) Description
Returns the arc-sine, in radians, of the numeric argument n between -1 and 1. Example
SELECT ASIN (0.5) "asin" FROM DUMMY; asin 0.5235987755982989 ATAN Syntax ATAN (n) Description
Returns the arc-tangent, in radians, of the numeric argument n. The range of n is unlimited. Example
SELECT ATAN (0.5) "atan" FROM DUMMY; atan
ATAN2 Syntax
ATAN2 (n, m) Description
Returns the arc-tangent, in radians, of the ratio of two numbers n and m. This produces the same result as ATAN(n/m).
Example
SELECT ATAN2 (1.0, 2.0) "atan2" FROM DUMMY; atan2 0.4636476090008061 BINTOHEX Syntax BINTOHEX (expression) Description
Converts a binary value to a hexadecimal value. Example
SELECT BINTOHEX('AB') "bintohex" FROM DUMMY; bintohex 4142 BITAND Syntax BITAND (n, m) Description
Performs an AND operation on the bits of the arguments n and m. Both n and m must be non-negative integers. The BITAND function returns a result with BIGINT type.
Example
SELECT BITAND (255, 123) "bitand" FROM DUMMY; bitand 123 CEIL Syntax CEIL (n) Description
Returns the first integer that is greater or equal to the value n. Example
