Storing multiple values in a column

Analytics
Enthusiast

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 C1
1 100,200,300
2 140,500,789,334,223,657

I 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.
6 REPLIES
Enthusiast

Re: Storing multiple values in a column

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.
Junior Supporter

Re: Storing multiple values in a column



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.
N/A

Re: Storing multiple values in a column

Carlos,

could you pls give the soluton with a example?

Thx!
Junior Supporter

Re: Storing multiple values in a column



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.

Re: Storing multiple values in a column

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 C
1 100
1 200
1 300
2 140
2 500
3 789

select * from T2 where outlet_id in (select c1 from T1 where record_type=1);

Thanks,
Eswar

Eswar

Re: Storing multiple values in a column

carlos this is good. thanks!