GETQUERYBANDVALUE

Translation specification for the transformation of GetQueryBandValue to Snowflake

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.

Description

The GetQueryBandValue function searches a name key inside of the query band and returns its associated value if present. It can be used to search inside the transaction, session, profile, or any of the key-value pairs of the query band.

For more information on this function check GetQueryBandValue in the Teradata documentation.

[SYSLIB.]GetQueryBandValue([QueryBandIn,] SearchType, Name);

Sample Source Patterns

Setup data

Teradata

Query

IN -> Teradata_01.sql

SET QUERY_BAND = 'hola=hello;adios=bye;' FOR SESSION;

Snowflake

Query

OUT -> Teradata_01.sql

ALTER SESSION SET QUERY_TAG = 'hola=hello;adios=bye;';

GetQueryBandValue with QueryBandIn parameter

Teradata

Query

IN -> Teradata_02.sql

SELECT
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

Result

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

Result

GetQueryBandValue without QueryBandIn parameter

Teradata

Query

IN -> Teradata_03.sql

SELECT
GETQUERYBANDVALUE(2, 'hola') as Example1,
GETQUERYBANDVALUE(2, 'adios') as Example2;

Result

Snowflake

Query

OUT -> Teradata_03.sql

SELECT
GETQUERYBANDVALUE_UDF('hola') as Example1,
GETQUERYBANDVALUE_UDF('adios') as Example2;

Result

Some parts in the output code are omitted for clarity reasons.

Known Issues

1. GetQueryBandValue without QueryBandIn parameter only supported for session

Teradata allows defining query bands at transaction, session or profile levels. If GetQueryBandValue is called without specifying an input query band Teradata will automatically check the transaction, session or profile query bands depending on the value of the SearchType parameter.

In Snowflake the closest equivalent to query bands are query tags, which can be specified for session, user and account.

Due to these differences, the implementation of GetQueryBandValue without QueryBandIn parameter only considers the session query tag and may not work as expected for other search types.

Related EWIs

No related EWIs.