Syntax elements used in an APPLY statement must appear in the order shown in the following syntax diagram.
|*||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 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.
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.
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):
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.
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]);