Teradata.lng - Teradata SQL Assistant

Teradata® SQL Assistant for Windows User Guide

Product
Teradata SQL Assistant
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-01-03
dita:mapPath
gfk1537201040714.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2430
Product Category
Teradata Tools and Utilities

This is an XML file that is used to identify each token within a query as a specific token type. When updating the file to support a newer version of Teradata Database the only changes that would generally be required would be to add new entries for the following token types:

MainKeywords These define primary statements, e.g., Select, Insert, Update.
ObjectTypes Object type names. If an object type does not appear in the list of object types any attempt to Create or Drop an object of that type will be identified as invalid SQL.
Keywords

Other Teradata keywords that are not ‘main’ keywords.

New additions are generally required only to support syntax highlighting.

Functions Built-in function names. Function names must be added to this section in addition to adding them to the appropriate *.mbr file in order for Quick Info or Quick Paste to recognize these functions.
DataTypes Data type names. Required only to support syntax highlighting.

Each token type has a TokenID, TokenKey and Style. These values are fixed and should not be changed. If you are defining a single word or regular expression, then you would specify a PatternValue within either an ExplicitValueGroup or a RegexPatternGroup, respectively. If you are defining a list of words that are all of the same type [most cases], then these words would be defined within an ExplicitPatterns section of an ExplicitPatternGroup. This section is simply a space separated list of words.

For example:

<ExplicitPatternGroup TokenID="28" TokenKey="ObjectTypes" Style="KeywordStyle"
			LookAhead="\W|\z" CaseSensitivity="AutoCorrect">
	<ExplicitPatterns>
		TABLE VIEW MACRO FUNCTION PROCEDURE USER
	</ExplicitPatterns>
</ExplicitPatternGroup>
<RegexPatternGroup  TokenID="28" TokenKey="ObjectTypes" Style="KeywordStyle"
			PatternValue="JOIN\s+INDEX" LookAhead="\W|\z" />

Each definition can have an optional LookBehind or LookAhead expression. These are regular expressions that must occur before, or after, the ‘pattern’ in order for that word to be considered as a match for that token type.

CaseSensitivity="AutoCorrect" forces matched words in your query to be automatically converted to the case that is defined in the language file. Currently, functions are defined using proper case and keywords are defined as uppercase. These changes will only be made to your query if you have the option Convert keywords to uppercase checked. This property can only be specified for Explicit patterns.

In most cases, the new words added to the Teradata SQL lexicon should simply be added to the appropriate list and no additions or changes will be required to the LookBehind or LookAhead expressions.

In a few cases—primarily for object types—you may need to use a regular expression if you need to add a word pair rather than a single word. For example: "JOIN\s+INDEX" rather than separately defining the words “JOIN” and “INDEX”. In this case, you would add a new line for “<RegexPatternGroup … >” by copying a similar line in the file. This ensures that when you create a Join Index the status bar says “Join Index Created” rather than saying “Join Created”.

Note that, in a very few cases, a word might be used in different contexts that require them to be identified differently. In most cases, the difference only effects syntax highlighting so misidentification can be ignored. There are a few cases where different usages of a word need to be correctly identified. The main concerns are:
  • Words that sometimes start or end a block and other times do not, e.g., Case...End versus End(period).
  • Words that are used as some kind of trigger within the application, e.g., From tablename versus Substring(x From 5).

In these cases, you may need to add or modify a ‘LookAhead’ or ‘LookBehind’ filter.

Example:

<ExplicitPatternGroup TokenID="31" TokenKey="Functions" Style="FunctionStyle" LookAhead="\s*[(]"
LookBehind="(?!(INTERVAL|TO) \s+)" CaseSensitivity="AutoCorrect">
	<ExplicitPatterns>
		Year Month Hour Minute Second
	</ExplicitPatterns>
</ExplicitPatternGroup>
<ExplicitPatternGroup TokenID="30" TokenKey="DataTypes" Style="DataTypeStyle" LookAhead="\W|\z" CaseSensitivity="AutoCorrect">
	<ExplicitPatterns>
		YEAR MONTH HOUR MINUTE SECOND
	</ExplicitPatterns>
</ExplicitPatternGroup>

The above states that these words will be classified as functions when they are followed by an open parenthesis and are not preceded by the word Interval or To. Otherwise, they will be classified as data types. The LookAhead in the second definition is to avoid matching part of a word; as in Seconds. Most pattern groups include this LookAhead if they do not have their own specific LookAhead.