Home » SQL & PL/SQL » SQL & PL/SQL » how to create a view of table in diff schemas (merged a 3rd time)
how to create a view of table in diff schemas (merged a 3rd time) [message #362442] Tue, 02 December 2008 05:32 Go to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Let me explain my problem

Using Oracle 10.2g

I am in a schema called HAGGIS.

There is a table table_name which exists in all_tables in SCHEMA COMQDHB.

I am able to create a view based on the entire table as

create view v1 as select table_name from all_tables where owner = 'COMQDHB'.

When i conduct a query on the table i.e table_name it contains

columns grade,student,teacher.

I want to create a view based on only 2 columns grade and student.

Or i created the view v2 on the entire table and i want to remove one column from the view.#

alter view v2 delte GRADE; error.

i am attaching the problem so that you can take a look.

The St Trinians school database (sn=9364097) has not followed the same design as the main database and contains confidential information about both students and staff. When we come to create a distributed database, this would potentially reveal personal information to the distributed database which is not appropriate.
The St Trinians database can be found in the comqdhb schema on the HAGGIS database. You can discover the available tables for comqdhb in HAGGIS using the following SQL:

SELECT table_name FROM all_tables WHERE owner=’COMQDHB’

Q.

Create two views in your own HAGGIS schema which will fragment the St Trinians staff table into two views which will allow the staff information to be kept securely. I.e. create views for the St Trinians School which will allow a head teacher to be able to see all information, other people will only be able to see student information and student exam results.

[Updated on: Tue, 02 December 2008 05:35]

Report message to a moderator

Re: Unable to create view based on table in different Schema help pls [message #362443 is a reply to message #362442] Tue, 02 December 2008 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is a table table_name which exists in all_tables in SCHEMA COMQDHB.

ALL_TABLES is a view, it does not contain anything.
The underlying table (sys.tab$, for instance) does not contain the table but its name (and parameters).

Quote:
am able to create a view based on the entire table as
create view v1 as select table_name from all_tables where owner = 'COMQDHB'.

You then create a view on ALL_TABLES not on COMQDHB one.

Quote:
Or i created the view v2 on the entire table and i want to remove one column from the view.#

alter view v2 delte GRADE; error.

Stop immediatly and read SQL Reference.
What you're doing and saying is meaningless.
Also read Database Concepts to understand what you do.

Regards
Michel
Re: Unable to create view based on table in different Schema help pls [message #362444 is a reply to message #362442] Tue, 02 December 2008 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've got things a little back to front.
There is a table table_name which exists in all_tables in SCHEMA COMQDHB.

I am able to create a view based on the entire table as

create view v1 as select table_name from all_tables where owner = 'COMQDHB'.

You haven't created a view on COMQDHB there, you've created a view on the system owned view ALL_TABLES.

You can use that basic syntax (full syntax is here )

You can't alter a view to remove a column, you have to either Drop the view and recreate it, or use the CREATE OR REPLACE syntax.

To drop the column you'll need to alter the SQL used by the view and remove the column from teh SELECT list
How to create a view of tables in different schema using database link (merged) [message #362655 is a reply to message #362442] Wed, 03 December 2008 10:13 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
I am in one Schema called Haggis and there is another schema called GUS.

I created a database link in Haggis to Gus saying create database link guslink using 'gus'.

I have to copy columns from the tables in the GUS.comqdhb from one schema to another schema how do it do that.

Question
Create a view called simpleGusStudent which uses the tables in the GUS.comqdhb database schema. The view should have the following attributes:
upn, dob, gender, examlevel, grade, academicYear

Kindly help.thanks.
Re: How to create a view of tables in different schema using database link (merged) [message #362697 is a reply to message #362655] Wed, 03 December 2008 15:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A database link? Why? If both schemas are in the same database, simple GRANT SELECT is enough.
SQL> connect scott/tiger
Connected.
SQL> grant select on emp to mike;

Grant succeeded.

SQL> connect mike/lion
Connected.
SQL> create view v_emp as
  2    select ename, job, sal
  3    from scott.emp;

View created.

SQL> select * from v_emp;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
ALLEN      SALESMAN        1600
...

Quote:
I have to copy columns from the tables in the GUS.comqdhb from one schema to another schema
What do you call "copy columns"? What is "gus", and what is "comqdhb" in "GUS.comqdhb"?
Quote:
... which uses the tables in the GUS.comqdhb database schema
The same question as above; if we know what is the "GUS.comqdhb database schema", we'll probably know what "tables in <bla bla> schema" are.

Actually, perhaps I should correct myself: if I understand what you are talking about, I might be able to help.
Re: How to create a view of tables in different schema using database link (merged) [message #363243 is a reply to message #362697] Fri, 05 December 2008 09:24 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Using Oracle 10.2g

Thank you so much for having the patience to explain

Select table_name from all_tables where owner='COMQDHB';
returns
Table_name
-----------------
GRADELEVELS
GRADEVALUES
SCHOOL
............

COMQDHB is a Database.It consists of a view all_tables in which there is a column called table_name .That column consists of fields which are the names of tables but not tables themselves.Just as in a table there might be the names of people but not people themselves.In order to look into the tables we need to query the tables directly by saying select * from COMQDHB.GRADE;
Gus and Haggis are 2 different databases on which we are given DML privileges.

I am facing another problem and kindly help me out with that i almost spent 3 hours trying to solve it .

Create a view called simpleGus which uses the tables in the GUS.comqdhb database schema. The view should have the following attributes:
upn, dob, gender, examlevel, grade, academicYear,......

I need to create a view which selects columns from different tables by using join

so I am saying
create view simpleGus(upn,dob,gender,examlevel,grade,academicYear,subject,sn) as
select s.UPN,s.DATEOFBIRTH,s.GENDER,g.LEVEL,g.GRADE,s.ACYR,sb.SUBJECTm,sc.SN from
COMQDHB.STUDENT s JOIN COMQDHB.GRADEVALUES g JOIN COMQDHB.SUBJECT sb JOIN COMQDHB.SCHOOL sc;

error invalid user.table.column, table.column, or column specification

I tried to split the statement to find the error and

create view v as select s.UPN,s.DATEOFBIRTH,s.GENDER from COMQDHB.STUDENT s ;

works it created a view v;

create view v1(l) as select g.LEVEL from COMQDHB.GRADEVALUES g;

error invalid user.table.column, table.column
So the error is with the GRADEVALUES TABLE.

I tried to create the copy of the table as

create table t1 as select * from COMQDHB.GRADEVALUES;
created.

select * from t1;
LEVEL GRADE VALUE
---------- ---------- ----------
g C 5
g D 4
g E 3
g F 2
g G 1
g U 0


sometimes i get the error CONNECT BY clause required in this query block

create view v1 as select level from t1;
error
CONNECT BY clause required in this query block

I just am lost here.

Kindly help.Thanks.
Re: how to create a view of table in diff schemas (merged a 3rd time) [message #363251 is a reply to message #362442] Fri, 05 December 2008 09:43 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off COMQDHB is the name of a schema not a database.. DB names do NOT appear in the owner column of all_tables that's for schema names.

Secondly - level is a reserved word in oracle. Having a column with that name is an exceedingly bad idea.

Thirdly - can you please, please, read the ORAFAQ forum guide, learn how to use codetags and use them.

After all, this:
create view simpleGus
(upn,dob,gender,examlevel,grade,academicYear,subject,sn) as 
select s.UPN, s.DATEOFBIRTH, s.GENDER, g.LEVEL, g.GRADE, s.ACYR, sb.SUBJECTm, sc.SN 
from COMQDHB.STUDENT s 
JOIN COMQDHB.GRADEVALUES g 
JOIN COMQDHB.SUBJECT sb 
JOIN COMQDHB.SCHOOL sc;

Is more readable than this:

create view simpleGus(upn,dob,gender,examlevel,grade,academicYear,subject,sn) as
select s.UPN,s.DATEOFBIRTH,s.GENDER,g.LEVEL,g.GRADE,s.ACYR,sb.SUBJECTm,sc.SN from
COMQDHB.STUDENT s JOIN COMQDHB.GRADEVALUES g JOIN COMQDHB.SUBJECT sb JOIN COMQDHB.SCHOOL sc;

Don't you think?


Fourth - I'm not particularly used to ansi join syntax but I'm fairly sure the way you're coding the joins there is utterly wrong. You don't appear to be telling oracle how the tables should be joined together.

[Updated on: Fri, 05 December 2008 09:46]

Report message to a moderator

Previous Topic: if or case within a select
Next Topic: Procedure to fetch values from a select
Goto Forum:
  


Current Time: Sat Dec 10 01:23:09 CST 2016

Total time taken to generate the page: 0.07701 seconds