GETQUERYBANDVALUE

Translation specification for the transformation of GetQueryBandValue to Snowflake

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

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

Snowflake

Query

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

GetQueryBandValue with QueryBandIn parameter

Teradata

Query

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

+----------+----------+----------+----------+----------+----------+
| EXAMPLE1 | EXAMPLE2 | EXAMPLE3 | EXAMPLE4 | EXAMPLE5 | EXAMPLE6 |
+----------+----------+----------+----------+----------+----------+
| Mark200  | Mark200  | SaraDB   | Peter3   | DbAdmin  |          |
+----------+----------+----------+----------+----------+----------+

Snowflake

Query

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

+----------+----------+----------+----------+----------+----------+
| EXAMPLE1 | EXAMPLE2 | EXAMPLE3 | EXAMPLE4 | EXAMPLE5 | EXAMPLE6 |
+----------+----------+----------+----------+----------+----------+
| Mark200  | Mark200  | SaraDB   | Peter3   | DbAdmin  |          |
+----------+----------+----------+----------+----------+----------+

GetQueryBandValue without QueryBandIn parameter

Teradata

Query

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

Result

+----------+----------+
| EXAMPLE1 | EXAMPLE2 |
+----------+----------+
| hello    | bye      |
+----------+----------+

Snowflake

Query

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

Result

+----------+----------+
| EXAMPLE1 | EXAMPLE2 |
+----------+----------+
| hello    | bye      |
+----------+----------+

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

1. MSCEWI1020: Custom UDF inserted.

2. MSCEWI2084: Transaction and profile level query tags not supported in Snowflake, referencing session query tag instead.