CREATE OR REPLACE TYPE emp_obj AS OBJECT ( last_name VARCHAR (50), first_name VARCHAR (50), birth_dt DATE ); / CREATE OR REPLACE TYPE emp_va AS VARRAY (999) OF emp_obj; / CREATE TABLE division ( division_id NUMBER (9) PRIMARY KEY, division_name VARCHAR2 (100), emps emp_va );I know how to get all the items in my VARRAY with a query like:
SELECT e.first_name FROM division d , TABLE (d.emps) e WHERE d.division_name = 'Sales';
You've already done the hard part, isolating one component of each entry in the VARRAY, and you've shown how to use it in a SELECT clause. You can use single elements from the VARRAY in the WHERE clause in exactly the same way:
SELECT DISTINCT d.division_name FROM division d , TABLE (d.emps) e WHERE e.first_name = 'John';That's the cleanest, easiest to understand way to get the results you want. Like many SQL statements, it does what you want, but it doesn't do it how you want it to: it doesn't stop after finding the first match within a single division. The solution below does quit looking after it finds the first match, so it will usually run faster:
SELECT division_name FROM division d WHERE EXISTS ( SELECT 'X' -- Why 'X'? See note below. FROM TABLE (d.emps) WHERE first_name = 'John' );Why 'X'? You could SELECT anything in the subquery: a literal (like I did) a column, SYSDATE, USER or a list of any kind(s) of values. The only crucial thing is whether or not the subquery returns any rows. SELECTing a literal is faster than SELECTing a real column or SYSDATE, so that's what I did.
This problem does not really revolve around the VARRAY: you could use the same techniques on plain old regular vanilla-flavored relational tables:
-- The straightforward but slower solution SELECT DISTINCT d.division_name FROM division d JOIN emp e USING (division_id) WHERE e.first_name = 'John'; -- The faster but more convoluted solution SELECT division_name FROM division d WHERE EXISTS ( SELECT 'X' FROM emp WHERE first_name = 'John' AND division_id = d.division_id );
If you want run the examples, or to experiment with related questions, you might want to copy these statements to populate the tables.
-- After creating the division table, as shown on the main page, -- the following INSERT statements can be used to populate it. -- The 'Sales' division will have two employees named John. INSERT INTO division ( division_id, division_name, emps ) VALUES ( 1, 'Sales', emp_va ( emp_obj ('Adams', 'John', '30-Oct-1735'), emp_obj ('Adams', 'Abigail', '11-Nov-1744'), emp_obj ('Brown', 'John', ' 9-May-1800') ) ); -- The 'Administration' division will have no employees named John. INSERT INTO division ( division_id, division_name, emps ) VALUES ( 2, 'Administration', emp_va ( emp_obj ('Bingen', 'Hildegard', '17-Sep-1098'), emp_obj ('Bush', 'George W.', ' 6-Jul-1946') ) ); -- Now the SELECT statements (based on the division table, only) -- on the main page will run. CREATE TABLE emp AS SELECT d.division_id , e.last_name , e.first_name , e.birth_dt FROM division d , TABLE (d.emps) e; -- Now the queries on the main page that use the emp table will work.
For More Information
This was first published in July 2003