table of tables SQL syntax query

From: barry <bbulsara23_at_hotmail.com>
Date: 13 Apr 2002 08:33:28 -0700
Message-ID: <747f1dec.0204130733.3eb6cfb8_at_posting.google.com>


Does anybody have an idea on how to correctly formulate an SQL query to retrieve only records in testtable that have one value of y being 1 or 3 (ie returning Larry and Peter)?
Thanks
Barry

CREATE OR REPLACE TYPE testtype AS TABLE OF NUMBER(6); /
CREATE TABLE testtable (x VARCHAR2(20), y testtype) NESTED TABLE y STORE AS testtableoftables;
COMMIT;
INSERT INTO testtable (x,y) VALUES
('Peter',testtype(3,3,5,102.4,6,10));

INSERT INTO testtable (x,y) VALUES ('Thomas',testtype(75,20,50));
INSERT INTO testtable (x)   VALUES ('Mary');
INSERT INTO testtable (x,y) VALUES ('Larry',testtype(402,1,20));
Received on Sat Apr 13 2002 - 17:33:28 CEST

Original text of this message