0

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 ;
  • 2
    Why all this mess of a query? Why not a simp!e LEFT JOIN? – ypercubeᵀᴹ Jan 18 '17 at 1:22
  • I have tried LEFT JOIN. It also gives ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table. error. – John L Jan 18 '17 at 14:19
  • "We'd like to have a query that.." - not possible if you don't have control over the inner query. – a_horse_with_no_name Jan 18 '17 at 17:27
  • @a_horse_with_no_name True. In this case though I know the resultant query should theoretically be key-preserved, but want to write my query in such a way that Oracle recognize the key-preservedness. – John L Jan 18 '17 at 18:15
  • It has been a week now, so I am rewarding the correct answer to @Balazs Papp, who came up with select rowid in the inner select clause first. – John L Jan 26 '17 at 18:06
2

No, it is not. It is not guaranteed (well, it is, with preftype=1, but that is in the subquery, and that is not good enough for the optimizer) that your query returns at most 1 row for each key value from the base table. You need a unique constraint on customerid to make this work:

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;

ERROR at line 3:
ORA-01445: cannot select ROWID from, or sample, a join view without a
key-preserved table

alter table pref add unique(customerid);

Table altered.

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;

no rows selected

This however may not be feasible for you, as a customer can have multiple different preftypes with your original design.

This works without the unique constraint (but not the same result):

alter table pref drop unique(customerid);

Table altered.

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, pref p
  where c.customerId = p.customerId (+)
  and p.preftype = 1
  ) vi;

no rows selected

Or be more specific with the rowid:

select vi.* from (
  select c.rowid as c_rowid, c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

no rows selected
| improve this answer | |
  • I tried your last query, unfortunately p.preftype = 1 in your last query cancels out the outer join. – John L Jan 18 '17 at 1:13
  • 2
    @John L Yeah, did not pay enough attention, added another version, though I do not know what you need the rowid for. – Balazs Papp Jan 18 '17 at 7:25
  • Yeah, I think getting rowid from the primary table should be a good solution in general case. 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. Wish I can upvote you. – John L Jan 18 '17 at 14:23
3

I don't see the reason for having doubly nested subqueries when a simple LEFT JOIN can give the same result:

select c.customerid, c.name, p.prefval   
       -- , c.rowid                        -- this errs
from customer c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Now, when we add c.rowid in the SELECT list, Oracle parser/optimizer is not able to identify that every row of customers is going to be joined with - at maximum - 1 row from pref. So it throws an error.

We can convert the LEFT JOIN to a subquery though - and no error is thrown:

-- query 2 --
select c.customerid, c.name, 
       ( select p.prefval
         from pref p
         where c.customerid = p.customerid
           and p.preftype = 1 
       ) as prefval,
       c.rowid                               -- but works here
from customer c ;

or putting the customers in a derived table and then left join to pref:

-- query 3 --
select c.customerid, c.name, p.prefval, c.rowid                       
from 
    (select c.*, c.rowid from customer c) c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Tested at rextester.com

| improve this answer | |
  • This will not work. I need to maintain the key-preserved property because we need to select rowid out of the whole query. – John L Jan 18 '17 at 14:18
  • 2
    See my edit about that. But curious, what do you want the rowids for? – ypercubeᵀᴹ Jan 18 '17 at 16:18
  • 2
    By the way, I did notice the wrapping. That's why my answer starts with "I see no reason for having doubly nested subqueries". There's no need for wrapping, as far as we can see. – ypercubeᵀᴹ Jan 18 '17 at 16:45
  • 1
    The last query from Balazs Papp seems to be a valid alternative, too, equivalent to my last 2 queries. What's the problem with either of those? – ypercubeᵀᴹ Jan 18 '17 at 16:51
  • 2
    If "my answers" refer to the comment: " 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.", that should be an edit into the question (and much better if it was from the beginning so we don't have to do all these discussions in comments.) It helps everyone to understand better and answer, if all the requirements are in the question. (just saying so you know for the next one). ORMs are PITA ;) – ypercubeᵀᴹ Jan 18 '17 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.