16.20 - SQLRestrictedWords_TBF Function - Teradata Database

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

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
------------------------------ ------------------ --------------- --------
...                            ...                ...             ... 
RESTRICTWORDS                  12.00              ?               N
RETAIN                         06.02              ?               N
REUSE                          06.02              ?               N
RU                             06.02              ?               N
SAMPLES                        06.02              ?               N
SEARCHSPACE                    06.02              ?               N
SECURITY                       06.02              ?               N
SEED                           06.02              ?               N
SELF                           06.02              ?               N
SERIALIZABLE                   06.02              ?               N
SHARE                          06.02              ?               N
SOURCE                         06.02              ?               N
SPECCHAR                       06.02              ?               N
SPL                            06.02              ?               N
SQLDATA                        12.00              13.10           N
...                            ...                ...             ...

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
------------------------------ ------------------ --------------- --------
NUMBER                         14.00              ?               R
NUMBER                         13.00              14.00           N

16.20 - SQLRestrictedWords_TBF Function - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Fundamentals