16.20 - IGNORE QUERY_BAND VALUES - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
Defines the set of name-value pairs to ignore if specified in a SET QUERY_BAND statement for all users assigned the profile. When a user with the profile issues a SET QUERY_BAND statement containing a name-value pair matching a IGNORE QUERY_BAND VALUES name-value pair, the system performs the following actions:
  • The matching name-value pair is ignored.
  • The query band is set with the remaining name-value pairs.
  • A warning message displays.

You must enclose the set of name=value pairs for the query band with APOSTROPHE characters (').

The number of name=value pairs is limited to the maximum length of the string, which is 4,096 UNICODE characters, including pad characters.

Pair names and values cannot contain any of the following characters:
  • SEMICOLON (;)
  • a null
Setting a pair_name to an empty string value indicates that a name-value pair with the pair_name and any value will be discarded. For example:
pair_name=;
To specify multiple values for the same pair_name, each name-value pair must be specified separately. For example:
    IGNORE QUERY_BAND VALUES = 
        ’TVSTemperature=HOT;TVSTemperature=WARM;’

If an APOSTROPHE character is embedded within a pair name or value, you must type it twice, to escape it. Otherwise, the system interprets it as a pair_name=pair_value string terminator.

IGNORE QUERY_BAND VALUES
Keywords to introduce query band options to ignore.
pair_name
Name component of a query band specification.
The maximum size for each pair_name is 128 UNICODE characters. For more information about database object names, see Teradata Vantage™ SQL Fundamentals, B035-1141.
pair_value
Value component of a query band specification.
pair_value can be up to 256 UNICODE characters in length.

Example: Creating a Profile to Set a Default Query Band Value and a Query Band Value to Ignore

In this example, the user's profile query band is set to TVSTemperature=COLD. The user can issue a SET QUERY_BAND statement setting a higher precedence name-value pair of TVSTemperature=WARM. However, if the user sets the query band to TVSTemperature=HOT, the name-value pair is discarded from the query band.
    CREATE PROFILE salesprofile AS,
          QUERY_BAND = ’TVSTemperature=COLD;’ DEFAULT,
          IGNORE QUERY_BAND VALUES =’TVSTemperature=HOT’;

Example: Creating a Profile with a System Default Query Band Value and a Query Band Value to Ignore

In this example, the profile QUERY_BAND does not contain a TVSTemperature name-value pair. With this profile, suppose the session has the system default TVSTemperature setting as defined in the related DBS Control parameter. The user can execute an application that sets the QUERY_BAND TVSTemperature to WARM or COLD.
    CREATE PROFILE salesprofile AS,
          QUERY_BAND = ‘GROUP=WestCoast;’ DEFAULT,
          IGNORE QUERY_BAND=’TVSTemperature=HOT;’;

Example: Creating a Profile that Ignores a pair-name with Any pair-value

This example specifies the name IMPORTANCE without a value, that is, an empty string. This causes a SET QUERY_BAND statement to discard the name-value with the matching name and any value.
    CREATE PROFILE testprofile AS
          IGNORE QUERY_BAND VALUES = ‘IMPORTANCE=;’;

Usage Notes

If a profile has a query band that is defined as NOT DEFAULT, it is redundant to define the IGNORE QUERY_BAND VALUES with name-value pairs having the same names as defined in the profile query band. The NOT DEFAULT profile query band causes the SET QUERY_BAND statement to discard any name-value pair matching the names in the profile query band.