ARRAY Constructor Expression without NEW | Teradata Vantage - Invoking the ARRAY Constructor Expression Without the NEW Keyword - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

You can invoke the ARRAY constructor expression without using the NEW keyword. This is compatible with Oracle syntax. When invoked without the NEW keyword, Vantage handles the expression like a UDF expression. This restricts the places an ARRAY constructor expression can be used to the same usage as a scalar UDF. This means that in some scenarios, the ARRAY constructor expression with NEW keyword can be used in an SQL statement where the ARRAY constructor expression without NEW keyword cannot. For example, you can use the ARRAY constructor expression with NEW keyword in the DEFAULT clause of a CREATE TABLE statement. However you cannot use the ARRAY constructor expression without NEW keyword in the DEFAULT clause.

Additionally, since the ARRAY constructor expression without NEW keyword is treated like a scalar UDF, the existing search order rules for UDFs will apply in this case. Vantage searches for UDFs in the following databases in the following order:

  1. Default database
  2. SYSLIB database
  3. TD_SYSFNLIB database
  4. SYSUDTLIB database

If a UDF exists in the default database, SYSLIB, or TD_SYSFNLIB and has the same name as a defined ARRAY data type, and if you invoke the ARRAY constructor expression without the NEW keyword, then Vantage invokes the UDF instead of the ARRAY constructor. To avoid this conflict, use the NEW keyword when invoking the ARRAY constructor.