TD 14.0 provides support for both one-dimensonal and multi-dimensional ARRAY data types. The following DDL creates an array called "colors_array" which can contain up to 20 elements. The element type is INTEGER.
CREATE TYPE colors_array as INTEGER ARRAY;
Also, there are a wide variety of new built-in system functions for array types for greater ease of use, including comparison functions and arithmetic operators.
Support for ANSI SQL:2008 system functions UNNEST, ARRAY_AGG, CARDINALITY, and concatenation operator is also included.
I am trying to implement arrays in stored procedures. But it gives error everytime we use select into clause in teradata. Can arrays be used in select into clause in stored procedure? I have seen only hardcode values in inserts using array data types which works. What if we want to use insert into select * from and select into in stored procedures. I was trying to select a column from a table which has a integer column and into a array data type column.
For eg. I need to do the following where col_array is defined as array of type number
(CREATE TYPE NUMBER_ARY AS NUMBER(15) ARRAY DEFAULT NULL;) and col1 is a integer column.
select col1 into :col_array[i] from tablea
But everytime it gives error
expected something between col_array and [.
No user-defined CAST AS ASSIGNMENT exists from column col_array to UDT NUMBER_ARY
Any ideas would be really helpful.
How can a single value be gone into an array? I think you are trying to simulate what oracle has. For example .....select fields bulk collect into varray type or you may want to use the forall ..first and last statement. Even in oracle also it has to match. There it has the keyword bulk collect.
In Teradata, I have never implemented this feature. I am not aware of this feature. But you can solve your problem by doing a simple select into with a specific where clause.