16.20 - GetQueryBandValue - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

Returns the value of the specified name in the current query band or NULL.

Definition

REPLACE FUNCTION SYSLIB.GetQueryBandValue
      (SearchType SMALLINT,
       QBName     TD_ANYTYPE
       )
    RETURNS VARCHAR (257)CHARACTER SET UNICODE
               .
               .
               .
;
REPLACE FUNCTION SYSLIB.GetQueryBandValue
      (QueryBandIn TD_ANYTYPE,
       SearchType  SMALLINT,
       QBName      TD_ANYTYPE
       )
    RETURNS VARCHAR (257)CHARACTER SET UNICODE
               .
               .
               .
;

Input Parameters

Input Parameter Description
QueryBandIn A query band string or NULL.
SearchType 0 = Return the value of the first name-value pair where name is specified by the QBName input argument. If the query band contains the name-value pairs for the transaction and session, the function searches the transaction name-value pairs first.

1= Search the transaction name-value pairs in the query band and return the value that corresponds to the name specified by the QBName input parameter.

2 = Search the session name-value pairs in the query band and return the value that corresponds to the name specified by the QBName input parameter.

3 = Search the profile name-value pairs in the query band and return the value that corresponds to the name specified by the QBName input parameter.

QBName Name in the query band pair to search for.

Usage Notes

The GetQueryBandValue functions are two overloaded functions. One requires the query band string to be passed in; the other uses internal calls to retrieve the query band.

You can use these functions to query the DBC.DBQLogTbl table based on the names and values specified in the QueryBand column.

Both functions are created in the SYSLIB database by the DEM DIP script. EXECUTE privileges are granted to PUBLIC by default. For more information, see Requirements for Using the API.

Output Parameter

This function returns the value of the specified name in the query band or NULL. If no matches are found, the return value is an empty string or NULL.

Example: Using GetQueryBandValue to Query the QueryBand Column in QryLogV

This example shows you how to query the DBC.DBQLogTbl table for all Teradata brands specified in the QueryBand column.

SELECT t1.queryband(FORMAT 'X(50)') FROM
  dbc.qrylogv t1
  WHERE GetQueryBandValue(t1.queryband, 0, 'brand') = 'teradata'
    AND username='qbuser' ORDER BY t1.queryid;

Result:

QueryBand
--------------------------------------------------
=T> game=monopoly;brand=teradata;
=T> game=monopoly;brand=teradata;
=T> game=monopoly;brand=teradata;
=T> game=monopoly;brand=teradata;

Example: Using GetQueryBandValue to Query DBQLogTbl (QryLogV)

This example shows how to query the DBC.DBQLogTbl table for all cat names specified in the QueryBand column.

SEL GetQueryBandValue(t1.queryband, 0, 'cat') FROM
  dbc.qrylogv t1 where
     GetQueryBandValue(t1.queryband, 0, 'CAT') IS NOT NULL
     AND username='qbuser' ORDER BY t1.queryid;

Result:

GetQueryBandValue(QueryBand,0,'cat')
------------------------------------------------------------------------
asta
asta
asta
asta
asta
asta
asta
sabella
sabella
sabella
sabella
sabella
sabella
sabella
sabella
sabella
sabella
sabella
sabella