All Forums Analytics
adash-7422 33 posts Joined 01/09
04 Oct 2009
Storing multiple values in a column

Hello,I want to store multiple values in a column so that when i use it in IN clause all values are picked up in it as separate entities..What I mean from above is that: Suppose I have a table T1 with column C1, the data should be present as:Record_Type C11 100,200,3002 140,500,789,334,223,657I want to use C1 as follows:select * from T2 where outlet_id in (select c1 from T1 where record_type=1);As far as i know there is no array in Teradata.Let me know how it can be achieved in Teradata.Thanks in advance.

pawan0608 101 posts Joined 12/07
05 Oct 2009

As far as I know, Terdata doesn't support any Collection data type like Oracle has VARRY and Nested Table. you have to work with two columns only.

CarlosAL 512 posts Joined 04/08
21 Oct 2009

What you are trying to do is AGAINST 1FN. You should NORMALIZE the column to another table and use sentences with IN or EXISTS.Cheers.Carlos.

kottur 1 post Joined 09/08
22 Oct 2009

Carlos,could you pls give the soluton with a example?Thx!

CarlosAL 512 posts Joined 04/08
22 Oct 2009

BTEQ -- Enter your DBC/SQL request or BTEQ command:CREATE TABLE DB_USER001.TEST1FN1(RECORD_TYPE SMALLINT NOT NULL, C_TXT VARCHAR(10)) UNIQUE PRIMARY INDEX(RECORD_TYPE); *** Table has been created. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:CREATE TABLE DB_USER001.TEST1FN2(RECORD_TYPE SMALLINT NOT NULL, C1 SMALLINT) PRIMARY INDEX(RECORD_TYPE); *** Table has been created. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:CREATE TABLE DB_USER001.TEST1FN3(OUTLET_ID SMALLINT NOT NULL) PRIMARY INDEX(OUTLET_ID); *** Table has been created. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN1(RECORD_TYPE, C_TXT) VALUES (1,'UNO'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN1(RECORD_TYPE, C_TXT) VALUES (2,'DOS'); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (1,100); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (1,200); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (1,300); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,140); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,500); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,789); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,334); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,223); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN2(RECORD_TYPE, C1) VALUES (2,657); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:INSERT INTO DB_USER001.TEST1FN3(OUTLET_ID) VALUES (200); *** Insert completed. One row added. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:SELECT * FROM DB_USER001.TEST1FN3 WHERE OUTLET_ID IN ( SELECT C1 FROM DB_USER001.TEST1FN2 WHERE RECORD_TYPE=1); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.OUTLET_ID--------- 200 BTEQ -- Enter your DBC/SQL request or BTEQ command:DROP TABLE DB_USER001.TEST1FN1; *** Table has been dropped. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:DROP TABLE DB_USER001.TEST1FN2; *** Table has been dropped. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:DROP TABLE DB_USER001.TEST1FN3; *** Table has been dropped. *** Total elapsed time was 1 second.Cheers.Carlos.

g.eswar 2 posts Joined 10/09
24 Oct 2009

Hi All array values you can store in the table T1 under column C only.when ever you are trying to select using record_type=1 it automatically selects the your expected entries but here record_type value will be repeated based on your enties in the column C .Record_type C1 1001 2001 3002 1402 5003 789select * from T2 where outlet_id in (select c1 from T1 where record_type=1);Thanks,Eswar

Thanks,
Eswar

TrueGeekHoney 1 post Joined 11/09
28 Nov 2009

carlos this is good. thanks!

You must sign in to leave a comment.