MISCELLANEOUS FUNCTIONS

COALESCE: 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.

Syntax: COALESCE (expression_list)
Example: SELECT COALESCE(‘A’,’B’,’C’) AS COAL_ESCE FROM DUMMY

Ans: A (first not null value).

SELECT COALESCE(NULL,’B’,’C’)AS COAL_ESCE FROM DUMMY

Ans: B (First not null value)

GREATEST: This function is used to return the greatest value among the arguments: n1, n2, …

Syntax: GREATEST (<argument> [{, <argument>}…])
Example: SELECT GREATEST(12,45,75,32) AS “HIGHEST VALUE” FROM DUMMY

Ans: 75 (Highest value from the given input values)

GROUPING: GROUPING function can be used with GROUPING SETS/ROLLUP/CUBE that return multiple levels of aggregations in a single result set.
GROUPING(column) returns 1 if column is used in grouping and 0 otherwise. The column of GROUPING must be an element of the GROUPING SETS.

Syntax: GROUPING(column_name)
Example:

GROUPING_ID: GROUPING_ID function can be used with GROUPING SETS/ROLLUP/CUBE that return multiple levels of aggregations in a single result set.

Syntax: GROUPING_ID(column_name_list)

IFNULL: This function is used to return the first not NULL input expression.
Returns expression1 if expression1 is not NULL.
Returns expression2 if expression1 is NULL.
Returns NULL if both input expressions are NULL.

Syntax: IFNULL (expression1, expression2)
Example: SELECT IFNULL(2,3) AS “IF NULL” FROM DUMMY

Ans: 2 (result is first value because it is not null)

SELECT IFNULL(NULL,3) AS “IF NULL” FROM DUMMY

Ans: 3 (result is second value because first value is null)

LEAST: This function is used to return the least value among the arguments: <n1>, <n2>…

Syntax: LEAST (<n1> [, <n2>]…)
Example: SELECT LEAST(57,32,47) AS “LEAST VALUE” FROM DUMMY

Ans: 32 (Least value among the input values)

MAP: This function is used to search for an expression within a set of search values and returns the corresponding result.
If the expression value is not found and default_result is defined, MAP returns default_result.
If the expression value is not found and default_result is not defined, MAP returns NULL.

Syntax: MAP (<expression>, <search>, <result> [{, <search>, <result>}…] [, default_result])
Example: SELECT MAP(‘A’,’A’,’B’,’C’) AS “MAP” FROM DUMMY

Ans: B (expression ‘A’ is same as ‘A’ so the results is ‘B’)

SELECT MAP(‘A’,’D’,’B’,’C’) AS “MAP” FROM DUMMY

Ans: C (expression A is not equal to ‘D’ so the result is ‘C’)

Note: we can also use CASE as an alternative function to MAP. This function is perfect replacement for DECODE function in ORACLE Database.

NULLIF: NULLIF compares the values of two input expressions. If the first expression equals the second expression, NULLIF returns NULL.
If expression1 does not equal expression2, NULLIF returns expression1.
If expression2 is NULL, NULLIF returns expression1.

Syntax: NULLIF (expression1, expression2)
Example: SELECT NULLIF(‘A’,’A’) AS “NULL IF” FROM DUMMY

Ans: null/blank (expression 1 is equal to expression 2)

SELECT NULLIF(‘A’,’B’) AS “NULL IF” FROM DUMMY

Ans: A (expression 1 is not equal to expression 2)

SESSION_CONTEXT: This function is used to return the value of session_variable assigned to the current user.

Syntax: SESSION_CONTEXT(session_variable)
Example: SELECT SESSION_CONTEXT(‘APPLICATIONUSER’) FROM DUMMY

Ans: SAP_STUDENT (Application user set for the current session)

Note: variables can be set manually using SET SESSION <Variable Name> = <Value>.

SESSION_USER: This function is used to return the user name of the current session.

Syntax: SESSION_USER
Example: SELECT SESION_USER AS “SESSION USER” FROM DUMMY

Ans: SAP_STUDENT (user for current session)

Note: This function is available only from HANA SP10.

SYSUUID: This function is used to return a new universally unique identifier, generated by the connected SAP HANA instance. Each call of SYSUUID returns a new UUID value. SYSUUID calls from multiple connections are internally serialized to guarantee unique value generation.

Syntax: SYSUUID
Example: SELECT SYSUUID AS “SYS UNIQUE NUMBER” FROM DUMMY

Ans: 55AEB003F1DE0B6EE10000007F000101 (unique number generated by the system).