16.10 - Syntax - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

Syntax elements used in an APPLY statement must appear in the order shown in the following syntax diagram.







































where:

Syntax Element Description
* Indicates that all the columns from the specified data source object must be selected.
|| This concatenation operator joins two character strings into one string.
$DATACONNECTOR_ CONSUMER The name of the Teradata PT-provided template for the DataConnector Consumer operator.
$DATACONNECTOR_ PRODUCER The name of the Teradata PT-provided template for the DataConnector Producer operator.
$DDL The name of the Teradata PT-provided template for the Data Definition Language (DDL) (standalone) operator.
$DELETER The name of the Teradata PT-provided template for the Update Standalone operator used for deleting table rows.
$EXPORT The name of the Teradata PT-provided template for the Export (producer) operator.
$FE_OUTMOD The name of the Teradata PT-provided template for the FastExport OUTMOD Adapter (consumer) operator.
$FILE_READER The more descriptive name of the Teradata PT-provided template for the DataConnector Producer operator.
$FILE_WRITER The more descriptive name of the Teradata PT-provided template for the DataConnector Consumer operator.
$FL_INMOD The name of the Teradata PT-provided template for the FastLoad INMOD Adapter (producer) operator.
$INSERT A Teradata PT job script "placeholder" for an SQL INSERT statement Teradata PT generates based on:
  • The column definitions of the identified DBS target table 'tableName', when specified, or
  • A target table that Teradata PT can identify through assignments to various conventional job variables or script assignments to relevant consumer operator attributes.

The generated SQL INSERT statement will replace the $INSERT placeholder in the job script.

$INSERTER The name of the Teradata PT-provided template for the SQL Inserter (consumer) operator.
$LOAD The name of the Teradata PT-provided template for the Load (consumer) operator.
$ML_INMOD The name of the Teradata PT-provided template for the MultiLoad INMOD Adapter (producer) operator.
$ODBC The name of the Teradata PT-provided template for the ODBC (producer) operator.
$OS_COMMAND The name of the Teradata PT-provided template for the OS Command (standalone) operator.
$SCHEMAP The name of the Teradata PT-provided template for the Schema Mapping (consumer) operator.
$SELECTOR The name of the Teradata PT-provided template for the SQL Selector (producer) operator.
$STREAM The name of the Teradata PT-provided template for the Stream (consumer) operator.
$UPDATE The name of the Teradata PT-provided template for the Update (consumer) operator.
AND Designates a Boolean “and” operation.
ARRAY SUPPORT Indicates whether or not the Array Support feature will be used for a DML group associated with the Stream operator in the APPLY statement.
  • The ON value tells the Stream operator to use the Array Support feature. ON is the default unless the attribute value has been reset.
  • The OFF values tells the Stream operator to not use the Array Support feature. If the ARRAY SUPPORT DML option is specified, it overrides the value of the Stream operator ArraySupport attribute.

If the ARRAY SUPPORT DML option is not specified, the default value for Array Support for the DML group is the value in the Stream operator ArraySupport attribute. Even if no value is specified for the Stream operator ArraySupport attribute, the feature is enabled if basic system requirements for the feature are present.

For more information on the Stream operator ArraySupport attribute, see Stream Operator.

If the DML statement is an UPSERT statement, it must be specified in the “atomic form.” This method employs a pair of INSERT . . . UPDATE statements, using the INSERT FOR MISSING ROWS option that immediately precedes the Array Support syntax. See Array Support.

Errors:

If the ARRAY SUPPORT option is specified without a valid value, the operation will terminate with an error.

If the value for ARRAY SUPPORT is set to ‘On’ and either Teradata Database or Teradata CLIv2 does not support the Array Support feature, the Stream operator will terminate with a fatal error.

The Array Support feature only applies to a single DML statement. If the ARRAY SUPPORT DML option is ON, the Stream operator will terminate with an error if it finds multiple DML statements within a single APPLY statement.

AS Optional. Introduces the column name associated with a derived column, which is a value that results from evaluating an expression.
attributeName The name of an operator attribute to which a value is assigned when job execution reaches this point in the job script.
attributeValue The value that is assigned to the operator attribute just specified, when job execution reaches this point in the job script.
attributeValue can also be an array for attributes that support array values. For a description of attributes that support array values, see the chapters on individual operators beginning with DataConnector Operator
ATTRIBUTES ATTR Optional. Introduces a list of value assignments for the operator referenced in an operator specification.
CASE In a case expression, CASE introduces one or more conditional expressions where the first (in left-to-right order) value with a true search condition is the value of the CASE expression. If none of the search conditions are true, the CASE expression value is the value of its ELSE expression, if present; otherwise, its value is NULL.

In a case DML expression, CASE introduces one or more conditional DML group where the first one (in left-to-right order) with a true search condition is applied to the target of the APPLY statement. If none of the search conditions are true and an ELSE clause is present, then its DML group are applied to the target(s); otherwise, no DML groups are applied.

CAST (NULL AS dataType) This built-in function returns the value NULL in the specified data type.
columnName Optional column name specification. The column name may already be specified, such as in the SCHEMA attribute of an operator referenced in the APPLY TO or SELECT FROM sections of the APPLY statement.
consumerOperatorName The name of a consumer operator defined in a DEFINE OPERATOR statement in the job script.
database Name of the database in Teradata Database where the macro to be executed.
DELETE/DEL Keyword indicating a DELETE statement is being executed by the macro.
'DMLstring' Required valid syntax and semantics for SQL INSERT, UPDATE, and DELETE statements.
ELSE In a case expression, the ELSE keyword introduces an expression where the value is the value of its case expression, if none of the case expression’s search conditions are true.

In a case DML expression, ELSE introduces a DML group that is applied if none of the search conditions in the case DML expression are true.

ELSE NULL The optional unconditional default NULL value of a case expression or the optional unconditional default “no action” case of a case DML expression, explicitly specified.
END In a case expression, END marks the end of the case expression.

In a case DML expression, END marks the end of a CASE group of conditional DML group.

EXECUTE The EXECUTE statement applies only for the TPT Stream operator.
FALSE Designates the Boolean “false” value specification.
filterOperatorName The name of a filter operator defined in a DEFINE OPERATOR statement in the job script.
FROM Designates the source for the select operation.
INSERT/INS Keyword indicating an INSERT statement is being executed by the macro.
INSERT FOR Applies the corresponding INSERT when the row to be UPDATEd is missing. Valid only for the Update and Stream operators.

This option applies only when the DML group consists of a single UPDATE statement followed by a single INSERT statement. These keywords are followed by one of the following keywords, which complete the specification of the option (and mean the same thing):

  • ROWS
  • MISSING UPDATE ROWS

Use the IGNORE MISSING UPDATE ROWS DML option when you use the INSERT FOR ROWS or INSERT FOR MISSING UPDATE option so that missing update rows are not inserted into the error table.

instanceCount Specifies the number of parallel operator instances. The default is 1. Required to specify multiple instances of the same object in a job step.
IS Designates a Boolean test.
macro name Name of the macro resident in Teradata Database to be executed.
mantissa 'E' exponent A numeric constant expressed in standard scientific notation.
MARK or IGNORE Directs that source rows be put in the error table (MARK) or not put in the error table (IGNORE) when these keywords are associated with certain condition, usually common DBS errors.
  • When followed by DUPLICATE, MARK directs that a row be put in the error table when it duplicates an existing row in the target table, as follows (valid for Stream and Update operators):
    • MARK DUPLICATE ROWS (for both insert and update operations)
    • MARK DUPLICATE INSERT ROWS (for insert operations only)
    • MARK DUPLICATE UPDATE ROWS (for update operations only)
    A row is a duplicate row if all column values in the row are exactly the same as those of another row. Duplicate row checking is bypassed if the table is a multiset table (which allows duplicate rows), or if the table has one or more unique indexes (the uniqueness test(s)) make any duplicate row check unnecessary); in these cases, IGNORE DUPLICATE ROWS has no effect. Any uniqueness violations result in the offending rows going to the error table.
  • When followed by MISSSING, MARK directs that a row be put in the error table when the row it targets for modification or deletion does not exist in the target table, as follows (valid for Stream and Update operators):
    • MARK MISSING ROWS (for both update and delete operations)
    • MARK MISSING UPDATE ROWS (for update operations only)
    • MARK MISSING DELETE ROWS (for delete operations only)
  • When followed by EXTRA, MARK directs that a row be put in the error table when the row modifies or deletes multiple rows in the target table, as follows (valid for Stream operator):
    • MARK EXTRA ROWS (for both update and delete operations)
    • MARK EXTRA UPDATE ROWS (for update operations only)
    • MARK EXTRA DELETE ROWS (for delete operations only)

For all the above cases, if IGNORE is specified instead of MARK, it means that the row should not be put in the error table.

nonQuote Character Any character other than the single-quote (') character
NOT Designates the Boolean negation.
NULL Designates that no element is to be applied.
NULL (dataType) Same as CAST (NULL as dataType).
OPERATOR Designates a Teradata PT operator.
OR Designates a Boolean “or” operation.
producerOperatorName The name of a producer operator defined in a DEFINE OPERATOR statement in the job script.
schemaName The name of a Teradata PT schema defined in a DEFINE SCHEMA statement in the job script.
SELECT Designates the select option of a Teradata PT data movement operation.
SERIALIZE ON Invokes the serialization feature, which guarantees that operations on a specified column occur serially. Valid only for the Stream operator.
standaloneOperatorName The name of a standalone operator defined in a DEFINE OPERATOR statement in the job script.
tableName The name of an existing DBS table whose column definitions will be the basis for a Teradata PT-generated DEFINE SCHEMA statement.
THEN In a case expression, THEN introduces the value expression corresponding to a search condition of a conditional expression.

In a case DML expression, THEN introduces a DML group that is applied when the search condition of the corresponding WHEN keyword is the first true condition encountered.

TO Designates the target(s) of an update operation.
TRUE Designates the Boolean “true” value specification.
UNION ALL Enables combination and parallel processing of data from multiple sources.
UNKNOWN Designates the value of a Boolean predicate whose truth or falsity cannot be determined because one or more operands are NULL-valued.
unsigned integer An integer that has no preceding “+” or “-” sign.
UPDATE/UPD Keyword indicating an UPDATE statement is being executed by the macro.
UPSERT/UPS Keyword indicating an Atomic upsert is being executed by the macro.
USE Introduces the names of the columns transmitted to the database when the SQL statements in a DML group are executed during APPLY processing. Valid only for the Stream operator.
userDefined ConsumerTemplateName The name of a user-created consumer operator template.
userDefinedFilterTemplateNa me The name of a user-created filter operator template.
userDefinedProducerTemplate Name The name of a user-created producer operator template.
userDefinedStandaloneTempl ateName The name of a user-created standalone operator template.
VIA Specifies the filter operator that filters/modifies/alters data after it is processed by a producer operator.

The VIA clause goes between the APPLY keyword and the SELECT keyword:

APPLY 
( ... 
) 
TO OPERATOR ( consumer_operator [ n]) 
VIA OPERATOR (filter_operator [n]) 
SELECT... FROM OPERATOR (producer_operator [n]);