Example: Using UPDATE With a FOR SESSION VOLATILE Query Band - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

This example sets a FOR SESSION VOLATILE query band with the UPDATE option, verifies that the query band set is what was intended, and then shows that the existing query band has been updated to add the name:value pairs area=west, city=sandiego, tree=maple, and flower=rose.

Below is the initial SET QUERY_BAND to set the query band and write to DBC.SessionTbl.

SET QUERY_BAND = 'area=west;city=sandiego;tree=maple;flower=rose;' FOR SESSION;

Following is the SET QUERY_BAND statement with the UPDATE option.

     SET QUERY_BAND = 'cat=siamese;dog=akita;' 
     UPDATE FOR SESSION VOLATILE;

Result:

      *** Set QUERY_BAND accepted. 
      *** Total elapsed time was 1 second.

The query band is now ‘cat=siamese;dog=akita;area=west;

city=sandiego;tree=maple;flower=rose’, as the following procedure call demonstrates.

     CALL syslib.GetQueryBandSP(qb);

Result:

      *** Procedure has been executed. 
      *** Total elapsed time was 1 second.
   QueryBand
   -------------------------------------------------------------------
   =S>    cat=siamese;dog=akita;area=west;city=sandiego;tree=maple;flower=rose;

There should only be one row in DBC.SessionTbl and it should be for the following query band.

'city=sandiego;flower=rose;area=west;cat=asta;tree=maple;' 

The following SELECT request shows that there is only one row in DBC.SessionTbl, and it is the row that was expected to be there.

     SELECT queryband 
     FROM DBC.SessionTbl 
     WHERE queryband IS NOT NULL 
     AND   CHAR_LENGTH(queryband) > 0;

Result:

      *** Query completed. One row found. One column returned. 
      *** Total elapsed time was 1 second.
     QueryBand
     -----------------------------------------------------------------
     city=sandiego;flower=rose;area=west;cat=asta;tree=maple;