15.00 - SQLRestrictedWords_TBF Function - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

SQLRestrictedWords_TBF Function

Purpose

SQLRestrictedWords_TBF is a table function that can be used to query the restricted words for the current or previous releases of Teradata Database. This is useful for migration or upgrade planning, or in cases when you want to back down to the previous Teradata Database release.

For each word, the table indicates the category of restriction (reserved, nonreserved, or future reserved), the Teradata Database release when the word was introduced or dropped as a restricted word, and whether the word is reserved, nonreserved, or neither in the current ANSI/ISO SQL standard.

SQLRestrictedWords_TBF is created in the SYSLIB database by the DIPDEM script, which is run automatically by the DIP utility when Teradata Database is installed.

Syntax

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Result

The output table includes these columns:

 

Column Name

Description

restricted_word

The restricted word.

release_introduced

The release when the restricted word was introduced.

The release number is presented in this format:

   MM.mm

where MM are 2 digits representing a major release number and mm are 2 digits representing a minor release number. For example, '06.02', '12.00', or '13.10'.

Note: The value of the release_introduced field for restricted words that were introduced in Teradata Database 6.2 or in earlier releases is '06.02'.

release_dropped

The release when the restricted word was dropped. The release number is in the same format as for the release_introduced column.

A NULL value indicates that the restricted word has not been dropped, and is currently a restricted word.

category

One of the following characters that represents the category of the Teradata restricted word:

  • R: a Teradata reserved word
  • F: a Teradata future reserved word
  • N: a Teradata nonreserved word
  • ANSI_restricted

    One of the following characters that represents the ANSI category of the restricted word:

  • R: an ANSI reserved word
  • N: an ANSI nonreserved word
  • T: a word that is neither reserved nor nonreserved by current ANSI standards, but that is restricted by Teradata
  • Usage Notes

    Table functions can be used only in the FROM clause of an SQL SELECT statement.

    For more information on:

  • table functions, see the discussion of Table UDFs in SQL Functions, Operators, Expressions, and Predicates
  • the use of table functions in queries, see the TABLE option of the FROM clause in SQL Data Manipulation Language
  • Queries using the SQLRestrictedWords_TBF function are case specific.

    Example : Getting the Restricted Words for a Specific Release

    The following query returns the restricted words for Teradata Database 12.0. Note that the query includes words that were restricted in all releases up to and including 12.0, and excludes words that were dropped as restricted words prior to release 12.0. A portion of the output is included below. Note also that the reference to release 12.0 must be entered in the query as ‘12.00’.

       SELECT * FROM TABLE (SYSLIB.SQLRestrictedWords_TBF()) AS t1
          WHERE release_introduced <= '12.00' AND
                (release_dropped > '12.00' OR release_dropped IS NULL);
    restricted_word                 release_introduced  release_dropped  category  ANSI_restricted
    ------------------------------  ------------------  ---------------  --------  ---------------
    SPL 06.02 ? N T
    SQLDATA                         12.00               13.10            N         N
    SQLSTATE                        06.02               ?                N         R
    AbortSessions                   12.00               ?                N         T
    GetQueryBand                    12.00               ?                N         T
    GetQueryBandValue               12.00               ?                N         T
    IdentifyDatabase                12.00               ?                N         T
    IdentifySession                 12.00               ?                N         T

    Note that even though SQLDATA was dropped as a restricted word for release 13.10, it was a restricted word for release 12.0, so it is returned by the query.

    Example : Double Entries in the Restricted Words Table

    It is possible to have two rows returned for a specific restricted word if the word was dropped and later reintroduced, or if the restriction category was changed for the word. In this example, the results show that NUMBER was changed from a nonreserved word to a reserved word in release 14.00.

       SELECT * FROM TABLE (SYSLIB.SQLRestrictedWords_TBF()) AS t1
          WHERE restricted_word = 'NUMBER';
    restricted_word                 release_introduced  release_dropped  category  ANSI_restricted
    ------------------------------  ------------------  ---------------  --------  ---------------
    NUMBER                          14.00               ?                R         N
    NUMBER                          13.00               14.00            N         N