ARRAY Structure in Teradata Stored Procedure

UDA
hba
Teradata Employee

ARRAY Structure in Teradata Stored Procedure

HI,

I am looking for details about object or datatype in Teradata stored procedure which will be work like ARRAY Structure. Is there any object available in TD which supports ARRAY kind structure?

Thanks in Advance.

Regards,
Harshad
14 REPLIES
Junior Contributor

Re: ARRAY Structure in Teradata Stored Procedure

Hi Harshad,
could you provide more details what you're trying to achieve? I can hardy think of any problem where an array is really needed, which can't be rewritten using SQL.

Faking an array is usually done using a temporary table, e.g.:
create volatile table fakearray(i int not null primary index, val ...);

Dieter
hba
Teradata Employee

Re: ARRAY Structure in Teradata Stored Procedure

Hi,

Thanks for the reply. I am trying to implement ARRAY Structure in TD Store procedure which will store data from cursor on the fly. Just want to know that whether is there any structure, object available in TD or not.

Thanks

HBA
Teradata Employee

Re: ARRAY Structure in Teradata Stored Procedure

Hello,

No, built-in there is no object for arrays, but as Dieter suggested, it can be done using temporary tables.

Regards,

Adeel
Enthusiast

Re: ARRAY Structure in Teradata Stored Procedure

Thank you for responding to the question on Arrays.

Can someone verify the approach I have taken to implement the array in Teradata based on the suggestion in the forum.

1) Create a volatile table with 4 columns

Index Column, Index Range, Table_Type, Table_Value
1 1 Employee Test 1 - his name
1 2 Employee 12131980 - his DOB
1 3 Employee Male - Sex

1 1 Country 001 - Country Code
1 2 Country USA - Country Name
Teradata Employee

Re: ARRAY Structure in Teradata Stored Procedure

There is an alternative on storing array data using UDT (User Defined Type) for array.
Teradata Employee

Re: ARRAY Structure in Teradata Stored Procedure

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[20];

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.

Enthusiast

Re: ARRAY Structure in Teradata Stored Procedure

Hi,

It is very much there , distinct and structure.

After defining your type say x_udt, you can insert values ....like 

insert into tab values(NEW x_udt().fieldx ('your values') .......));

Cheers,

Raja

Teradata Employee

Re: ARRAY Structure in Teradata Stored Procedure

Hi,

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[20] 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.

Thanks,

Kruti

Enthusiast

Re: ARRAY Structure in Teradata Stored Procedure

Hi Krutz,

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. 

Cheers,

Raja