asked on

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

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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.