Querying a list and a external datasource: /home/Support/LabKey Support Forum

Querying a list and a external datasource

LabKey Support Forum (Inactive)

View Message

Querying a list and a external datasource

bront

2015-08-12 11:22

Status: Closed

hi,

I have created list within a project, and I would like to create a query that joins that list to a table which is accessed via an external schema.

I can see the list data in the LabKey UI, but when I try to query via:

SELECT u.user
FROM "/Care_Plans/".lists."users" u

I get the following error:

Bad SQL grammar. Select command denied to user: ExternalSchemaUser@...

The ExternalSchemaUser is the name of the external data source, so maybe it's not possible to connect a list with an external datasource?

We're on 15.1 with PostgreSQL 9.1.18.

Many thanks,

bront

Jon (LabKey DevOps) responded:	2015-08-12 17:41
Hi Bront, This is unfortunately true. In our documentation here, we even state that: "Note that you cannot create joins across data sources, including joins between external and internal schema on LabKey Server." https://www.labkey.org/wiki/home/Documentation/page.view?name=externalSchemas The only way I've ever seen joins like that work on external datasources is when the external datasource is used as a synonym if you're using MS SQL rather than Postgres. So you could potentially mount tables and views from a different schema or DB, but have them work in LabKey as if they were local. https://msdn.microsoft.com/en-us/library/ms187552.aspx Regards, Jon