tech_question
asked on
can I join views in Oralce ?
I have most of my stuff in sql server 2000, now I have a project in Oralce. Can I join three views in Oracle ?
I have three views , test1_view , test2_view, test3_view
test1_view - custID
test2_view - custID
test2_view - prodID
test3_view - prodID
I need to join (inner join) all these three views test1_view custID and test2_view on custID,
test2_view and test3_view on prodID. Also how can I give aliases to these views in oracle ?
I have three views , test1_view , test2_view, test3_view
test1_view - custID
test2_view - custID
test2_view - prodID
test3_view - prodID
I need to join (inner join) all these three views test1_view custID and test2_view on custID,
test2_view and test3_view on prodID. Also how can I give aliases to these views in oracle ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am sorry I should have posted this too. test2_view , test3_view are under a different schema/username. I am executing under the current schema(testuser1) which has test1_view. I have appended the schema to the other two views - testuser2.test2_view and testuser3.test3_view to the above query you gave - but I am getting a invalid table name error. I have executed each of the individual views under the current schema/user (testuser1). I do not get any error. Any thoughts as to why ?
Oracle supports the optional "as" keyword.
and for the different schema question... You need to "grant select on <view_name> to <user_name>;" as the owner of the <view_name> in order for the join to work. You might also need to grant select to the tables the view uses, but this is not always the case.
ASKER
I definitely have the select permissions on these views. When I select *from these views I get all the rows. Now coming to permissions on the tables of these views, would there be a case where I select them I get all the rows and I join them I need to have select permissions on these tables ? is this the case ?
This might be, because if you're using contents of only one table of a view, the CBO might decide to drop the view alltogether to get a more suitable execution plan. This would mean that you were no longer accessing the view, but the actual table instead, which you of course need to have permission to do before it succeeds.
can you show the query/code you have right now?
and, of course, the outcome / errors you get...
and, of course, the outcome / errors you get...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...
((test1_view v1
inner join test2_view v2 on v1.custID=v2.custiD)
inner join test3_vew v3 on v2.ProdID = v3.ProdID)
where ...