can I join views in Oralce ?

Posted on 2007-10-14
Medium Priority
Last Modified: 2013-12-07
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 ?

Question by:tech_question
  • 3
  • 2
  • 2
  • +2
LVL 42

Assisted Solution

dqmq earned 800 total points
ID: 20075118
Hmmm...I thought MSSQL and Oracle used the same join syntax.  I guess Oracle does not support the optional "as keyword", so just leave it out.  Or, you can always do it the old-fashioned way.

... from
test1_view v1
,test2_view v2
,test3_vew v3
where v1.custID=v2.custiD
and v2.ProdID = v3.ProdID
LVL 42

Expert Comment

ID: 20075125
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 ...

Author Comment

ID: 20075331
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 ?

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 20075332
Oracle supports the optional "as" keyword.

Expert Comment

ID: 20075335
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.

Author Comment

ID: 20075353
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 ?

Expert Comment

ID: 20075366
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.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20076177
can you show the query/code you have right now?
and, of course, the outcome / errors you get...
LVL 74

Accepted Solution

sdstuber earned 1200 total points
ID: 20079780
  FROM test1_view t1,
            test2_view t2,
            test3_view t3
 WHERE t1.custid = t2.custid
      AND t2.prodid = t3.prodid

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question