GETQUERYBANDVALUE_UDF (VARCHAR, FLOAT, VARCHAR)

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

This is the user-defined function (UDF) overloaded with three parameters.

Definition

Retrieves the value associated with a name inside the name-value pairs of the transaction, session, or profile query band.

GETQUERYBANDVALUE_UDF(QUERYBAND VARCHAR, SEARCHTYPE FLOAT, SEARCHNAME VARCHAR)

Parameters

QUERYBAND VARCHAR

The query band which can be a concatenation of transaction, session, and profile query bands.

SEARCHTYPE FLOAT

The level of the pairs that will be searched for a match.

0 = Any.

1 = Transaction.

2 = Session.

3 = Profile.

SEARCHNAME VARCHAR

The name key to search in the pairs.

Returns

The value associated with the name key at the specific level or null if not found.

Usage example

Input:

SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 2, 'account');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'role');
SELECT GETQUERYBANDVALUE_UDF('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 1, 'role');

Output:

      Matt
      SaraDB
      DbAdmin
      NULL

Migration example

Input:

SELECT GETQUERYBANDVALUE('=T> account=Matt;user=Matt200; =S> account=SaraDB;user=Sara;role=DbAdmin;', 0, 'account')

Output:

WITH
--** MSC-WARNING - MSCEWI2078 - THE EXPAND ON CLAUSE FUNCTIONALITY IS TRANSFORMED INTO A CTE BLOCK **
ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, duration) bg
FROM
project,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', duration))))
)
SELECT NORMALIZE emp_id,
duration
FROM
project,
ExpandOnCTE;