Syntax - Parallel Transporter

• The column definitions of the identified 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.

• 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 the database or 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.

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 targets; otherwise, no DML groups are applied.

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.

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

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.

• 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 tests) 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.

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.

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]);