Joining two tables from two separate SQL Connections: References to an SQL

Question

Paul Riker · Aug 14, 2019

SQLCONNECT1 = tableUsers

SQLCONNECT2=tableLogEntries

select * from tableusers u, tablelogentries e where e.userid = u.userid

I keep getting the error References to an SQL Connection must constitute a whole subquery

Discussion (1)1

Matthew Giesmann · Aug 14, 2019

From the docs on the SQL Gateway
"All the tables listed in the FROM clause of an SQL query must come from the same data source. Queries that join data from heterogeneous data sources are not allowed."
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?K...

You might be able to use a sub query to work around this:
select * from tableusers u JOIN (SELECT * FROM tablelogentries) e ON e.userid = u.userid

I would contact the WRC if the above workaround doesn't cover all of your use cases.

1 0

Joining two tables from two separate SQL Connections: References to an SQL Connection must constitute a whole subquery