Creating a query/join with tables from two different schemas.

1026398

Hello All, I'm working on creating a query/join with tables from two different schemas. 2 tables are from 1 schema and another table is from a different schema. Any general advice on how to create the join would be much appreciated. Thank you.

Frank Kulash

Hi,

Qualify the table names with the owner name.  For example, don't just say EMP; say SCOTT.EMP (or whatever).

You must do this for all the tables that are not in your current scema, but it's a good idea to do it for all tables in a query that uses tables from different schemas.

For example:

SELECT  e.ename, e.empno, e.hiredate
,       j.job, j.descrip
FROM    my_schema.job  j
JOIN    scott.emp      e  ON  e.job  = j.job
;

Make sure you have SELECT privileges on all the tables you don't own.

1026398

Hi Frank - using the query below I am receiving the following error:

SELECT

s.commission_date, o.install_repair_complete_date, o.service_order_id, c.case_id,

s.hns_customer_id, c.fso, c.provider_group_id,

c.provider_group_name, c.site_id

FROM

TS.TS_SITE s, TS.TS_CASE c, DV.|DV_SERVICE_ORDER o

JOIN

WHERE

c.site_id=s.site_id

and c.case_id=o.case_id

and (c.provider_group_id = 'CABLE1' or c.provider_group_id = 'CABLE2' or c.provider_group_id = 'CABLE3')

and o.install_repair_complete_date between TO_DATE('03-16-2014','mm-dd-yyyy') and TO_DATE('03-23-2014','mm-dd-yyyy')

ERROR:

ORA-00942: table or view does not exist

00942. 00000 -  "table or view does not exist"

*Cause:  

*Action:

Error at Line: 6 Column: 32 - located at the red tilda

I wasn't quite sure how to implement using your method or if what I wrote was essentially the same or not? I've seen various methods. I ran a simple query with the DV schema/table and returned results indicating that I do have SELECT priviledges for that schema. Thoughts? Thank you in advance for your multiple replies to my multiple questions.

Frank Kulash

Hi,

Sometimes "table or view does not exist" means there is no such table, and  sometimes it means you don't have any privileges on the table.

Are you sure you successfully queried that same table?  Did you do it in the same session where you're now getting the error?  Post the query that confirms you have privileges on DV.DV_SERVICE_ORDER.

Are you connected as as TS when you get the error?  Can you log in as DV and grant privileges again:

GRANT  SELECT ON dv_service_order TO ts;

?  (It won't do any harm to grant the same privilege 2 or more times.)

Are you doing this in PL/SQL, or as part of a CREATE VIEW statement?  Privileges granted through roles don't count in those cases.

1026398

Hi Frank,

Yes I am positive that I successfully queried the DV table. It was the same session. I queried that table alone and it did return a result set so I'm assuming that means that both the table exists and that I do have SELECT priviledges. I am off from work tomorrow but I will be back in early Friday morning. I will run the query again and post the query after it returns results. If I recall correctly I just wrote a very simple:

SELECT *

FROM DV_SERVICE_ORDER

where

creation_date between TO_DATE('03-16-2014','mm-dd-yyyy') and TO_DATE('03-23-2014','mm-dd-yyyy')

and the system returned a result set. In terms of being connected as TS ... geez I know that I'm working within our (non optimized) warehouse but the connection doesn't offer schema related connection/login as far as I've seen. Could I be missing something there and is this even an issue if when I query the table without a join I receive a result set? It's only when creating a join that I am having an issue.

* I am a department of 1 with essentially no one to help me and no access to the DB's, believe it or not.

After hopefully rectifying this portion then I'm tasked with comparing this date to another date and finding out if the dates are within 5 days of each other. That was one of my other posted questions but one at a time I guess.

Side note: Although I have lived In Florida for the last 10 years I was born in Brighton, MA and grew up mostly in Everett, MA. I see that you're also from Mass. Thanks again.

Frank Kulash

Hi,

8e2e82ee-c8d1-495d-83a3-391e1c86327d wrote:

Hi Frank,


Yes I am positive that I successfully queried the DV table. It was the same session. I queried that table alone and it did return a result set so I'm assuming that means that both the table exists and that I do have SELECT priviledges. I am off from work tomorrow but I will be back in early Friday morning. I will run the query again and post the query after it returns results. If I recall correctly I just wrote a very simple:


SELECT *
FROM DV_SERVICE_ORDER
where
creation_date between TO_DATE('03-16-2014','mm-dd-yyyy') and TO_DATE('03-23-2014','mm-dd-yyyy')
and the system returned a result set. ...

This is using a table called

DV_SERVICE_ORDER in the current schema (or maybe a synonym called DV_SERVICE_ORDER).  The query with the problem tried to use

DV.DV_SERVICE_ORDER.

Are you sure the DV_SERVICE_ORDER table that you want in the join is in the DV schema?  To see which schemas have tables called DV_SERVICE_ORDER, run

SELECT    owner
FROM      all_tables
WHERE     table_name  = 'DV_SERVICE_ORDER'    -- Use CAPITAL letters inside quotes
ORDER BY  owner;

If DV_SERVICE_ORDER (without any owner qualification) works in one query, then use it in the other as well.

In terms of being connected as TS ... geez I know that I'm working within our (non optimized) warehouse but the connection doesn't offer schema related connection/login as far as I've seen. Could I be missing something there and is this even an issue if when I query the table without a join I receive a result set? It's only when creating a join that I am having an issue.

When you're using Oracle, you're always connected as a particular user, and you always have a current schema (almost always the same user).  Your front end must be hiding that, so that it's not obvious.

* I am a department of 1 with essentially no one to help me and no access to the DB's, believe it or not.

Are there DBAs, but you're just not allowed to talk to them?  Then talk to whoever can change that, and point out that it's causing you to waste your time.  Nobody knows all the answers all the time; sometimes you'll have to ask questions.  When the questions concern your system (as this one does), then it's much more efficient for you to ask people who know your system, rather than people like me who don't.

After hopefully rectifying this portion then I'm tasked with comparing this date to another date and finding out if the dates are within 5 days of each other. That was one of my other posted questions but one at a time I guess.

Exactly!  After you solve this problem, start a new thread.  Include CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.

Side note: Although I have lived In Florida for the last 10 years I was born in Brighton, MA and grew up mostly in Everett, MA. I see that you're also from Mass. Thanks again.

Small world!  Until 10 years ago, I lived in Allston (or, as real estate people called it, Brighton.  Brighton is much classier.)