SQL Miscellaneous Functions in SAP HANA with Examples and syntax:-
The SQL Miscellaneous function in SAP HANA contains something different then the SQL SERVER.Below all the SQL Miscellaneous functions are explained one by one with examples and with syntax.
1.Syntax: COALESCE (expression_list): This function is used to return the first non-NULL expression from a list. At least two expressions must be contained in expression_list, and all expressions must be comparable. The result will be NULL if all the arguments are NULL.
Exp: SELECT COALESCE(‘A’,’B’,’C’) AS COAL_ESCE FROM DUMMY Output: A (first not null value). Exp.SELECT COALESCE(NULL,’B’,’C’)AS COAL_ESCE FROM DUMMY output: B (First not null value)
2.CURRENT_CONNECTION: This function is used to return the id of the current connection.Example: SELECT
Example: SELECT CURRENT_CONNECTION AS “CURRENT CONNECTION” FROM DUMMY Ans: 213947 (ID of the current connetion)
3.CURRENT_IDENTITY_VALUE(): This function is used to return a BIGINT value representing the latest inserted identity value in the current session. If no identity value was inserted in the current session, NULL will be returned as a result.
Example: SELECT CURRENT_IDENTITY_VALUE() FROM DUMMY Ans: 234 (This is the current identity value for that session)
4.CURRENT_SCHEMA: This function is used to return a string containing the current schema name.
Example: SELECT CURRENT_SCHEMA AS “CURRENT SCHEMA” FROM DUMMY Ans: SAP_STUDENT (this is the current schema set for the session)
5.CURRENT_TRANSACTION_ISOLATION_LEVEL: This function is used to return the current write statement sequence number of the current transaction. This function is available from HANA SPS10.
Example: SELECT CURRENT_UPDATE_STATEMENT_SEQUENCE () FROM DUMMY Ans: 3 (update statement sequence in the sql session)
6.CURRENT_UPDATE_TRANSACTION ( ): This function is used to return the id of the current update transaction. This function is available from HANA SPS10.
Example: SELECT CURRENT_UPDATE_TRANSACTION() AS “CURRENT UPDATE TRANSACTION” FROM DUMMY Ans: 2 ( update transaction in that section).
7. CURRENT_USER: This function is used to return the current username at the current statement context. This will be user name which is currently at the top of authorization stack.
Example: SELECT CURRENT_USER AS “CURRENT USER” FROM DUMMY