GetQueryBandValueSF - Teradata Database - Teradata Vantage NewSQL Engine

Application Programming Reference

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
vwf1492987142269.ditamap
dita:ditavalPath
changebar_rev_16_20_exclude_audience_ie.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

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

Definition

CREATE FUNCTION TD_SYSFNLIB.GetQueryBandValueSF(
    QueryBandIn TD_ANYTYPE,
    SearchType  SMALLINT,
    QBName      TD_ANYTYPE)
 RETURNS VARCHAR(257) CHARACTER SET UNICODE
          .
          .
          .
;

CREATE FUNCTION TD_SYSFNLIB.GetQueryBandValueSF(
    SearchType  SMALLINT,
    QBName      TD_ANYTYPE)
 RETURNS VARCHAR(257) CHARACTER SET UNICODE
          .
          .
          .
;

Input Arguments

Input Argument Description
QueryBandIn A query band string or NULL.
SearchType

A numeric argument 0-3 specifying which query band type to search for the name.

0 = Return the value of the first name-value pair where name is specified by the QBName input argument. If the query band contains 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 argument.

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 argument.

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. The function returns the value in the name-value pair. A query band name is a maximum of 128 characters.

Usage Notes

The GetQueryBandValueSF function provides similar functionality to the GetQueryBandValue function. For more information about this interface, see GetQueryBandValue.

The GetQueryBandValueSF 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.

This function allows NULL input argument and return values.

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

Return Value

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 GetQueryBandValueSF to Query 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.dbqlogtbl t1
  WHERE GetQueryBandValueSF(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 GetQueryBandValueSF to Query DBQLogTbl (QryLogV)

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

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

Result:

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