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 ;
5
  • 2
    Why all this mess of a query? Why not a simp!e LEFT JOIN? Jan 18, 2017 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, 2017 at 14:19
  • "We'd like to have a query that.." - not possible if you don't have control over the inner query.
    – user1822
    Jan 18, 2017 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, 2017 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, 2017 at 18:06

2 Answers 2

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
3
  • I tried your last query, unfortunately p.preftype = 1 in your last query cancels out the outer join.
    – John L
    Jan 18, 2017 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. Jan 18, 2017 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, 2017 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

9
  • 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, 2017 at 14:18
  • 2
    See my edit about that. But curious, what do you want the rowids for? Jan 18, 2017 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. Jan 18, 2017 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? Jan 18, 2017 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 ;) Jan 18, 2017 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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