Syntax Elements - Teradata® Database

query_expression

SELECT statement, including a table operator SELECT call.

For information about the syntax of the SELECT clause, ON clause, PARTITION BY clause, DIMENSION clause, and ORDER BY clause, see the chapter on the SELECT statement in SQL Data Manipulation Language , B035-1146 .

For information about the supported symbol predicates, see the chapter on Logical Predicates in SQL Functions, Operators, Expressions, and Predicates, B035-1145.

ON Clause

ON table_name

Name of input table.

ON view_name

Name of input view.

ON query_expression

Expression that resolves to an input table.

AS alias_name

Alias name for the input table.

PARTITION BY column_name

Name of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and can have additional partitioned input tables.

PARTITION BY column_position

Position of the column by which every partitioned input table is partitioned. The function requires at least one partitioned input table, and you can specify additional partitioned input tables.

DIMENSION

You can specify additional DIMENSION input tables.

ORDER BY column_name

Name of the column by which every input table is ordered.

ORDER BY column_position

Position of the column to use for ordering the results.

ORDER BY sort_expression

Expression to use for ordering the results.

ASC

Sort results in ascending order.

DESC

Sort results in descending order.

NULLS FIRST

Sort nulls first in results.

NULLS LAST

Sort nulls last in results.

MODE

Specifies the pattern-matching mode.

OVERLAPPING

The function finds every occurrence of the pattern in the partition, regardless of whether the instance is part of a previously found match. Therefore, one row can match multiple symbols in a given matched pattern.

NONOVERLAPPING

The function begins the next pattern search at the row that follows the last pattern match. This is the default behavior of commonly used pattern matching utilities, including the UNIX grep utility.

PATTERN

Specifies the pattern for which the function searches.

symbolic_search_pattern

You compose symbolic_search_pattern with the symbols that you define in the SYMBOLS argument, operators, and parentheses.

To specify that a subpattern must appear a specific number of times, use the Range-Matching Feature defined later in this section. For pattern matching details, refer to Pattern Matching.

The following table describes the simplest patterns, which you can combine to form more complex patterns. When patterns have multiple operators, the function applies them in order of precedence, and applies operators of equal precedence from left to right. The following table also shows operator precedence. To force the function to evaluate a subpattern first, enclose the subpattern in parentheses. In the following table, A and B are symbols defined in the SYMBOLS argument.

SYMBOLS

Defines the symbols that appear in the values of the PATTERN and RESULT arguments.

SYMBOLS (
   pagetype = 'homepage' AS H,
   pagetype <> 'homepage' AND pagetype <>
'checkout' AS PP,
   pagetype = 'checkout' AS CO
)

For more information about symbols that appear in the PATTERN argument value, refer to Symbols. For more information about symbols that appear in the RESULT argument value, refer to Result: Applying Aggregate Functions.

logical_predicate

An expression whose value is a column name. If col_expr represents a column that appears in multiple input tables, then you must qualify the ambiguous column name with its table name. For example:

Symbols (
   weblog.pagetype = 'homepage' AS H,
   weblog.pagetype = 'thankyou' AS T,
   ads.adname = 'xmaspromo' AS X,
   ads.adname = 'realtorpromo' AS R
)

AS symbol

Any valid identifier. The symbol is case-insensitive. However, a symbol of one or two uppercase letters is easier to identify in patterns.

symbol_predicate

SQL predicate, often a column name.

RESULT

Defines the output columns.

aggregate_function

The function to apply. For details, see Result: Applying Aggregate Functions. The function evaluates this argument once for every matched pattern in the partition. That is, the function outputs one row for each pattern match.

expression

An expression whose value is a column name. The expression specifies the values to retrieve from the matched rows.

symbol_list

Each symbol represents all the rows that matched the predicate of that symbol in this particular matched PATTERN.

The list can include a single symbol, or can be a comma separated list of more than one. For a single symbol list, ANY and the parentheses are optional. For example, OF A, OF ANY (A), and OF ANY (A,B) are valid for symbol_list, but OF (A,B) and OF (A) are not.

AS column_name

Determined by RESULTS argument. See Result: Applying Aggregate Functions.

Contains data to search for patterns.

Range-Matching Feature

   (subpattern){n[,[m]]}

(subpattern){n}

Subpattern must appear exactly n times.

For example, the following pattern specifies that subpattern (A.B|C) must appear exactly 3 times:

   'X.(Y.Z).(A.B|C){3}'

The preceding pattern is equivalent to the following pattern:

   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C)'

(subpattern){n,}

Subpattern must appear at least n times. For example, the following pattern specifies that subpattern (A.B|C) must appear at least 4 times:

   'X.(Y.Z).(A.B|C){4,}'

The preceding pattern is equivalent to the following pattern:

   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C).(A.B|C)*'

(subpattern){n,m}

Specifies that subpattern must appear at least n times and at most m times. For example, the following pattern specifies that subpattern (A.B|C) must appear at least 2 times and at most 4 times:

   'X.(Y.Z).(A.B|C){2,4}'

The preceding pattern is equivalent to the following pattern:

   'X.(Y.Z).(A.B|C).(A.B|C).(A.B|C)?.(A.B|C)?'

Output