SQL Miscellaneous Functions in SAP HANA with Syntax and Examples

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)

[AdSense-A]

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).

[AdSense-A]

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.