All Forums UDA
hba 3 posts Joined 06/08
18 Nov 2008
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

dnoeth 4628 posts Joined 11/04
18 Nov 2008

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

Dieter

hba 3 posts Joined 06/08
18 Nov 2008

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

Adeel Chaudhry 773 posts Joined 04/08
18 Nov 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

13 Feb 2010

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_Value1 1 Employee Test 1 - his name1 2 Employee 12131980 - his DOB1 3 Employee Male - Sex1 1 Country 001 - Country Code 1 2 Country USA - Country Name

masroor 2 posts Joined 05/09
29 Jun 2011

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

angelicag 1 post Joined 04/12
05 Apr 2012

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.

 

 

Raja_KT 1246 posts Joined 07/09
03 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

krutz 3 posts Joined 03/13
20 Nov 2013

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
 

Raja_KT 1246 posts Joined 07/09
20 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

krutz 3 posts Joined 03/13
20 Nov 2013

Hi Raja,
Thanks for the response. I was trying to do similar to what you said. I am trying to do an oracle to teradata conversion. I tried this using
select NEW NUMBER_ARY((SEL CAST( col1 AS VARCHAR(11)) AS col1 FROM tablea) into :col_array;
How to insert multiple values into an array column?
For example I want to insert 1,2,3 into col_array[1], col_array[2] and col_array[3], then can I am not able to insert it directly through an select into clause. These values 1,2,3 come from a table which has integer datatype for 1,2,3. Tried converting columns to rows and then insert entire (1,2,3) into col_array , but then it inserts 123 into col_array[1] only. Is there any other way we can convert this similar to oracle? Inserting single values is possible. But how multiple values can be inserted. I tried it using for loop too but it didnt work.
Thanks,
Kruti

tk8590 1 post Joined 10/12
21 Nov 2013

hi,
To insert values into the the variable defined as array from the column of the table can be done using the array_agg function.
1) define the variable as the array datatype that you hav created.
2) set the variable as array like
SET id=prcs_id_array();
wheer id is the variable
prcs_id_array is the varray type
3)then to insert values into the array 

SET id =(SELECT  ARRAY_AGG(ETL_PRCS_EXCTN_ID, NEW prcs_id_array())

      FROM ETL_PRCS_EXCTN_EV );

where ETL_PRCS_EXCTN_ID is the column of the table ETL_PRCS_EXCTN_EV of which u want the values into the array.

 

for more info abt the array_agg function refer to link

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/ARRAY_Functions.081.024.html

 

 

 

 

Raja_KT 1246 posts Joined 07/09
21 Nov 2013

Hi Krutz,

 

I am not able to understand it clearly. However, if you want to convert columns into rows then you can do thus.

 

select col1 from A

union all 

select col2 from A

union all

select col3 from A

 

Now having said this and otherwise, if you want to limit your query you can use a where clause and run in a loop. To select one value at a time, your table must have other fields to limit and get only that value. Teradata has: open cursor,loop, fetch , select field into :var1 and other features , similar to oracle. You can implement the same logic.

I see and recommend that if you are migrating from Oracle, sybase, MS sql etc to teradata, please see business and logic and implement it. Dont go by the syntax.

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

krutz 3 posts Joined 03/13
22 Nov 2013

Hi tk8590,
This is exactly what I needed. Thanks for the reply.
Hi Raja,
Thanks for the reply. I was trying to accomplish select into an array data type column from a normal integer column into a procedure. It seems this will help me. I wanted to convert this just for understanding the use of arrays in procedure from an available code in oracle to teradata. We did it using VT and other ways but wanted to do by using arrays only to check if it works.
Thanks,
Kruti
 

Raja_KT 1246 posts Joined 07/09
22 Nov 2013

You are welcome. Good luck.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.