SQLRestrictedWords_TBF Function | SQL Fundamentals | Teradata Vantage - SQLRestrictedWords_TBF Function - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

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

For each word, the table indicates the category of restriction (reserved, nonreserved, or future reserved), the 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 Advanced SQL Engine is installed.

Syntax

[ SYSLIB. ] SQLRestrictedWords_TBF()

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-eserved 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

Related Topics

For more information on:
  • Table functions, see the discussion of Table UDFs in Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
  • The use of table functions in queries, see the TABLE option of the FROM clause in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.