I have access to two databases, each with a separate connection. The first connection is an .sde connection to a SDE 10.3.1 Oracle database, and the second connection is a .odc OLE DB connection to a third party software Oracle database. Is it possible to use both databases/connections in a query layer? I have some fairly complex non-spatial SQL queries I'd like to do using both databases; the SQL includes subqueries, group by, etc.
My guess is that the answer is no; it looks like you can only select one connection when creating a query layer. I've worked through a few alternatives, but they all seem to be lacking in one way or another:
Create a query layer on a table from a single connection/database, then create an in-map join to a table from the other database/connection using the layer properties in ArcMap. This is better than nothing, but is severely limited. I can't do any of the SQL querying that I need to do such as subqueries, group by, etc.
Use the Make Query Table geoprocessing tool, which sems to be able to reference multiple connections/databases. This has similar limitations to #1. You can enter an expression, but I believe the expression is the equivalent to the WHERE clause in SQL, which is limited. I can't do subqueries, group by, etc. here either.
I don't believe it's possible to create a view in one database that would reference another database.