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.

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.

Carlos,

could you pls give the soluton with a example?

Thx!

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.

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

carlos this is good. thanks!