I have a table of customers, and a table of customer preferences, where:
create table customer ( customerId number, name varchar2(200), primary key (customerId) ); create table pref ( preftype number, customerId number, prefval varchar2(200), primary key (preftype, customerId) );
So basically the pref table records the customer's preference based on each preference type. customerId is unique for each preftype.
Theoretically the following join should not lose the key-preserved property, but I am getting "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table." Anyway I can rewrite the query to maintain the key-preservedness?
select rowid, vi.* from ( select c.customerId, c.name, p.prefval from customer c, (select * from pref where preftype = 1) p where c.customerId = p.customerId (+) ) vi;
Restrictions / requirements:
In our case a query engine is building that outer query (the one with rowid) around the inner query, so we don't have as much control.
We'd like to have a query that can be wrapped around with something like:
select rowid, v.* from ( -- query ) v ;