Link to home
Start Free TrialLog in
Avatar of tech_question
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 ?

SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I mean the optional "as" keyword:

...
((test1_view v1
inner join test2_view v2 on  v1.custID=v2.custiD)
inner join test3_vew v3 on v2.ProdID = v3.ProdID)
where ...
Avatar of tech_question
tech_question

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.
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.
Avatar of Guy Hengel [angelIII / a3]
can you show the query/code you have right now?
and, of course, the outcome / errors you get...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial