Union Query between different schemas 
Author Message
 Union Query between different schemas

I have two users set up:  UserFRF ans UserEOD.  They both have the same
tables but
hold different information based on region.  I need to do the following:

SELECT Office, Region Officer FROM UserFRF.tbOfficertable
UNION
SELECT Office, Region Officer FROM UserEOD.tbOfficertable

I get an error saying UserEOD.tbOfficertable not found (not sure what the
real message was)

I am doing this statement in User "UserFRF" and I am user version 8.0 on my
test server
and it also does not work on 8i on the production server.

Question: Can this be done?  I need this to be a view for our website.

Sally



Sat, 19 Jul 2003 19:57:31 GMT
 Union Query between different schemas



Quote:
> I have two users set up:  UserFRF ans UserEOD.  They both have the same
> tables but
> hold different information based on region.  I need to do the following:

> SELECT Office, Region Officer FROM UserFRF.tbOfficertable
> UNION
> SELECT Office, Region Officer FROM UserEOD.tbOfficertable

> I get an error saying UserEOD.tbOfficertable not found (not sure what the
> real message was)

> I am doing this statement in User "UserFRF" and I am user version 8.0 on my
> test server
> and it also does not work on 8i on the production server.

> Question: Can this be done?  I need this to be a view for our website.

> Sally

UserFRF must have the "select" privilege on UserEOD tables
or the system privilege "select any table".

--
Have a nice day
Michel



Sat, 19 Jul 2003 20:34:51 GMT
 Union Query between different schemas
Hi Sally,

Michel's answer is correct..to do this, assuming you don't want to grant
select privileges on every table, you just log in as one user and grant
select on the table to the other user..that second user will then be
able to run the query.

You would have to do the same thing in reverse to allow both users to
run the query..there are various other alternatives..you could explore
using public SYNONYMS or create a view based on the query and grant
select privileges on the view...lots of options.

I hope this helps Sally,

Steve



Quote:
> I have two users set up:  UserFRF ans UserEOD.  They both have the
same
> tables but
> hold different information based on region.  I need to do the
following:

> SELECT Office, Region Officer FROM UserFRF.tbOfficertable
> UNION
> SELECT Office, Region Officer FROM UserEOD.tbOfficertable

> I get an error saying UserEOD.tbOfficertable not found (not sure what
the
> real message was)

> I am doing this statement in User "UserFRF" and I am user version 8.0
on my
> test server
> and it also does not work on 8i on the production server.

> Question: Can this be done?  I need this to be a view for our website.

> Sally

Sent via Deja.com
http://www.deja.com/


Sat, 19 Jul 2003 21:17:47 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Union query between two different schemas

2. Different execution plans for similar schemas on same query

3. Problems with different schemas in ORACLE

4. Do FROM items of different schemas conflict?

5. RFD: schemas and different kinds of Postgres objects

6. RFD: schemas and different kinds of Postgres objects

7. Insert into table from different schemas

8. Foreign key references to tables in different schemas ??

9. Same table name, different schemas

10. Creating Views joining tables in two different schemas

11. Designer 2.1 (Tables in different schemas)

12. How do you compare the data in different schemas not just table diffs


 
Powered by phpBB® Forum Software