dbTalk Databases Forums  

Creating view problem

Go Back dbTalk Databases Forums Databases comp.database.oracle Creating view problem

comp.database.oracle comp.database.oracle


Discuss Creating view problem in the comp.database.oracle forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
viki
 
Posts: n/a

Default Creating view problem - 04-29-2004 , 03:25 AM





Hi!

We have a problem: we need to create a view what is referencing to another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor



Reply With Quote
  #2  
Old   
Jim Kennedy
 
Posts: n/a

Default Re: Creating view problem - 04-29-2004 , 07:02 PM






"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote

Quote:
Hi!

We have a problem: we need to create a view what is referencing to another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying tables to
select.
Jim




Reply With Quote
  #3  
Old   
viki
 
Posts: n/a

Default Re: Creating view problem - 04-30-2004 , 07:29 AM


She has all rights(admin), and the select is working good, but when taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..
Quote:
"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is referencing to
another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying tables
to
select.
Jim



Reply With Quote
  #4  
Old   
Jim Kennedy
 
Posts: n/a

Default Re: Creating view problem - 04-30-2004 , 11:02 PM



"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote

Quote:
She has all rights(admin), and the select is working good, but when taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is referencing to
another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying tables
to
select.
Jim




So can the person do:
select * from schema2.table;
and
select * from schema1.table;

Then in sqlplus do
desc v1

Do you get the first 2 results and not the 3rd? Then grant select on the
3rd. Also what schema did you create the view in? maybe you need to do
select * from schema_the_view_in.v1;
Jim




Reply With Quote
  #5  
Old   
viki
 
Posts: n/a

Default Re: Creating view problem - 05-03-2004 , 03:24 AM


The select is working, i can reference to the either database(shema), but
only in the select not in the view.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat írta
a következő hírüzenetben: 2jFkc.2434$TD4.260254 (AT) attbi_s01 (DOT) ..
Quote:
"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:40924889 (AT) andromeda (DOT) datanet.hu...
She has all rights(admin), and the select is working good, but when
taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is referencing to
another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying
tables
to
select.
Jim




So can the person do:
select * from schema2.table;
and
select * from schema1.table;

Then in sqlplus do
desc v1

Do you get the first 2 results and not the 3rd? Then grant select on the
3rd. Also what schema did you create the view in? maybe you need to do
select * from schema_the_view_in.v1;
Jim



Reply With Quote
  #6  
Old   
Jim Kennedy
 
Posts: n/a

Default Re: Creating view problem - 05-03-2004 , 09:00 PM



"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote

Quote:
The select is working, i can reference to the either database(shema), but
only in the select not in the view.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 2jFkc.2434$TD4.260254 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:40924889 (AT) andromeda (DOT) datanet.hu...
She has all rights(admin), and the select is working good, but when
taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is referencing to
another
shema, but in same server. Here is a little example what we want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2 from
schema1.table,schema2.table where key=(select schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying
tables
to
select.
Jim




So can the person do:
select * from schema2.table;
and
select * from schema1.table;

Then in sqlplus do
desc v1

Do you get the first 2 results and not the 3rd? Then grant select on
the
3rd. Also what schema did you create the view in? maybe you need to do
select * from schema_the_view_in.v1;
Jim
You have asked for assistance. Why don't you put the results in the post so
we can see what is going on? What was the result of
desc v1
Jim




Reply With Quote
  #7  
Old   
viki
 
Posts: n/a

Default Re: Creating view problem - 05-05-2004 , 02:24 AM


Because i cannot create a view! (the desc is not working - "not enabled
statament")


"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat írta
a következő hírüzenetben: xOClc.17994$IG1.709045 (AT) attbi_s04 (DOT) ..
Quote:
"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:409603a4 (AT) andromeda (DOT) datanet.hu...
The select is working, i can reference to the either database(shema),
but
only in the select not in the view.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 2jFkc.2434$TD4.260254 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:40924889 (AT) andromeda (DOT) datanet.hu...
She has all rights(admin), and the select is working good, but when
taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az
alábbiakat
írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is referencing
to
another
shema, but in same server. Here is a little example what we
want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2
from
schema1.table,schema2.table where key=(select schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the underlying
tables
to
select.
Jim




So can the person do:
select * from schema2.table;
and
select * from schema1.table;

Then in sqlplus do
desc v1

Do you get the first 2 results and not the 3rd? Then grant select on
the
3rd. Also what schema did you create the view in? maybe you need to
do
select * from schema_the_view_in.v1;
Jim





You have asked for assistance. Why don't you put the results in the post
so
we can see what is going on? What was the result of
desc v1
Jim



Reply With Quote
  #8  
Old   
Jim Kennedy
 
Posts: n/a

Default Re: Creating view problem - 05-05-2004 , 09:18 PM


Provide more details. What tool are you using? I asked you to do it in
sqlplus and I don't think you are.
try
create or replace view v1 as ... ;

and
how about

desc schema1.table

and

desc schema2.table

What are the results of those? In sqlplus.
Jim


"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote

Quote:
Because i cannot create a view! (the desc is not working - "not enabled
statament")


"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: xOClc.17994$IG1.709045 (AT) attbi_s04 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:409603a4 (AT) andromeda (DOT) datanet.hu...
The select is working, i can reference to the either database(shema),
but
only in the select not in the view.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az alábbiakat
írta
a következő hírüzenetben: 2jFkc.2434$TD4.260254 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:40924889 (AT) andromeda (DOT) datanet.hu...
She has all rights(admin), and the select is working good, but
when
taking
it into a view always got an error.

"Jim Kennedy" <kennedy-downwithspammersfamily (AT) attbi (DOT) net> az
alábbiakat
írta
a következő hírüzenetben: 3Igkc.788$NI6.157346 (AT) attbi_s01 (DOT) ..

"viki" <nospam.vnemeth (AT) szintezis (DOT) hu> wrote in message
news:4090be0a (AT) andromeda (DOT) datanet.hu...
Hi!

We have a problem: we need to create a view what is
referencing
to
another
shema, but in same server. Here is a little example what we
want:

"select schema1.table.field1,schema2.table.field1 from
schema1.table,schema2.table where key=(select
schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

But, when we take the select into view:

"create v1 (f1,f2) as
select schema1.table.field1 as f1,schema2.table.field1 as f2
from
schema1.table,schema2.table where key=(select
schema2.table.key
from
schema2.table where schema2.table.key=schema1.table.key)"

we always got an error massage "table or view does',t exist"

Anybody can help me ?

Thanks,
Viktor


The user that is doing the select needs a grant on the
underlying
tables
to
select.
Jim




So can the person do:
select * from schema2.table;
and
select * from schema1.table;

Then in sqlplus do
desc v1

Do you get the first 2 results and not the 3rd? Then grant select
on
the
3rd. Also what schema did you create the view in? maybe you need
to
do
select * from schema_the_view_in.v1;
Jim





You have asked for assistance. Why don't you put the results in the
post
so
we can see what is going on? What was the result of
desc v1
Jim



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top