Multiple data sources in Oracle SQL Query

In the following query, and  I am able to successfully pull all the necessary information in sql developer.
I would like to pull some information from another table in another database.

SELECT usr.user_id,
       usr.name,
       usr.first_name,
       usr.last_name,
       usr.emp_id_varchar,
       loc.location_code,
       loc.name,
       lty.name
       
FROM MSCM.default_user_location_type dfl,
     MSCM.users usr,
     MSCM.location loc,
     MSCM.location_type lty
     
WHERE usr.USER_ID = dfl.user_id (+)
AND   dfl.location_type_id = lty.location_type_id
AND   dfl.location_id = loc.location_id
--AND   usr.emp_id_varchar = '20771'

ORDER BY usr.user_id,
         lty.location_type_id

I tried adding the following code in the from section, but it didn't work.
PROD.Employee emp

Any suggestions on how I can get this to work?

Thanks

Chris
metalteckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
You can only query one database at a time.

What you need to do is create a database link in the database that you are querying from.  Then you can use that to reference the table in the other database.

Something like:

Employee@PROD emp

Documentation for database links is here -> http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
I see a logic flaw in your query, and I have a question.  First, the question when you say "another table in another database" do you use the word "database" there with the meaning that this word has in an Oracle context, or do you use it with the meaning that is has in SQL Server?  The word "database" has different meanings in these two systems.  What is called a "database" in SQL server is usually called a "schema" in Oracle.  The suggestion from johnsone assumes that you used this word with the meaning it usually has in Oracle.  But, you did not indicate how this table should be related to the other tables in your query.

Now, the logic flaw: Your "where" clause contains one outer-join line:
"usr.USER_ID = dfl.user_id (+)"
but this same table (dfl) is joined to two other tables with no outer join operator.  That is inconsistent.  Because, if there really are missing records in this table (and you need the outer join to make sure that records from the usr table are included anyway) there is no way that the missing record(s) will have values that match the value in these other two tables.  Or conversely, if the records exist to match the value in the other two tables, then you don't need the outer join operator on that first "where" clause line.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.