|
|
|
Junior Member
 
Group: Forum Members
Last Login: 10/4/2009 11:15:58 PM
Posts: 14,
Visits: 45
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2 days ago @ 7:45:03 AM
Posts: 39,
Visits: 169
|
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/22/2009 10:24:19 AM
Posts: 39,
Visits: 213
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/22/2009 2:57:55 AM
Posts: 1,
Visits: 8
|
|
Carlos,
could you pls give the soluton with a example?
Thx!
Regards,
Kotti.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/22/2009 10:24:19 AM
Posts: 39,
Visits: 213
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/28/2009 12:33:17 PM
Posts: 2,
Visits: 17
|
|
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
Teradata consultant
|
|
|
|