IdentifiersThere are differences in the way Teradata SQL Engine and Teradata ML Engine interpret identifiers:
|Identifier||Teradata SQL Engine Interpretation||Teradata ML Engine Interpretation|
|Defining regular identifiers. For example, mixedCaseIdentifier.||Case is preserved: mixedCaseIdentifier.||Case is not preserved. The identifier is stored as lowercase: mixedcaseidentifier.|
|Using regular identifiers. For example, defined as mixedCaseIdentifier.||Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.||Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.|
|Defining delimited identifiers. For example, "mixedCaseIdentifier".||Case is preserved: mixedCaseIdentifier.||Case is preserved: mixedCaseIdentifier.|
|Using delimited identifiers. For example, defined as "mixedCaseIdentifier".||Not case sensitive. These are all equivalent: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.||Case sensitive. These are all different: mixedCaseIdentifier, mixedcaseidentifier, MIXEDCASEIDENTIFIER.|
When a Teradata ML Engine function execution request is sent to the Teradata ML Engine, the identifiers are represented as they were defined. However, there is no indication of whether they were defined as regular or delimited identifiers. On the Teradata ML Engine, function execution creates all objects using delimited names.
To avoid problems with identifiers, follow these rules:
- In tables to be used as input to a Teradata ML Engine function execution request, use only lowercase letters, digits, and underscores in column names.
- If you cannot observe the preceding rule (for example, when an existing table has column names that break the rule), give rule-breaking columns correlation names that have only lowercase letters, digits, and underscores. Reference these columns by their correlation names in function argument clauses.
For example, in the following query, the table ville_climatedata has two mixed-case column names, temp_F and dewpoint_F. They have lowercase correlation names in the SELECT list, and the InputColumns clause references them by their correlation names.
SELECT * FROM DWT ( ON (SELECT city, period, temp_F AS temp_f, pressure_mbar, dewpoint_F AS dewpoint_f FROM ville_climatedata ) AS InputTable PARTITION BY 1 OUT TABLE OutputTable (dwt_coef_table) OUT TABLE MetaTable (dwt_meta_table) USING InputColumns ('temp_f','pressure_mbar','dewpoint_f') SortColumn ('period') PartitionColumns ('city') Wavelet ('db2') Level (2) ) AS dwtout;Alternatively, you can enclose a rule-breaking column name in double quotation marks if it meets these requirements:
For example, in the following query, Name and Period need correlation names because they appear in the ON clause, but StockPrice needs only to be enclosed in double quotation marks, because it appears only in an argument clause and is not passed to a function output table.
- It does not appear in an ON clause in the SQL syntax.
- It is not passed to a function output table.
SELECT * FROM SimpleMovAvg ( ON (SELECT id, "Name" AS name, "Period" AS period, "StockPrice" FROM ibm_stock_upper ) PARTITION BY name ORDER BY period USING TargetColumns ('"StockPrice"') WindowSize ('10') IncludeFirst ('true') ) AS dt;
Query Output Alias
The length of a query output alias must not exceed 25 characters. For example, this alias causes an error:
SELECT * FROM KMeans ( ON (SELECT * FROM kmeansample) as InputTable USING OutputTable ('kmeanssample_centroid') NumberK ('3') Threshold ('0.01') MaxIterNum ('10') ) AS Long_Function_Name_Alias_Example;