August 3, 2004 at 9:36 am
I have a query that uses a view and that view uses data from two different tables.
When I run a query that uses that view and then join it, I get the following error:
Outer join operators cannot be specified in a query containing joined tables.
example view:
create view v_view1(emp_num, name)
as
Select t1.emp_num, t2.emp_name from number t1 inner join name t2
on t1.emp_num = t2.emp_name
example query
select t1.sales, t2.emp_name from sales t1 left outer join v_view1 t2
on t1.emp_num = t2.emp_num
Please help!
Thanks
August 3, 2004 at 10:23 am
Ensure you are in ANSI SQL-92 mode, ensure you use the modern method of joining i.e. the one you described with INNER JOIN and LEFT OUTER JOIN, and if you are sure of these, then the quickest way left to get it to work is to change your view, or your query to use the old school join syntax.
e.g. from t1, t2 where t1.xid = t2.xid as opposed to from t1 inner join t2 on t1.xid = t2.xid.
Don't know why you are specifically having a problem right now, as it looks fine to me, but your problem lies in this area.
Dave.
August 4, 2004 at 8:17 am
It should work (in this case) if you use OUTER JOIN in the view. (I assume that join expression t1.emp_num = t2.emp_name is a typo and should be t1.emp_num = t2.emp_num)
If view doesn't return too many rows you may use it as a derived table:
select t1.sales, t2.emp_name
from sales t1 left outer join (select emp_num, emp_name from v_view1) t2
on t1.emp_num = t2.emp_num
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply