16.10 - SQLRestrictedWords_TBF Function - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

Purpose

SQLRestrictedWords_TBF is a table function that can be used to query the restricted wordsfor the current or previous releases of Teradata Database. This is useful for migration orupgrade 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 	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

Related Topics

For more information on:

  • Table functions, see the discussion of Table UDFs in SQL Functions, Operators, Expressions, and Predicates, B035-1145.
  • The use of table functions in queries, see the TABLE option of the FROM clause in SQL Data Manipulation Language , B035-1146 .