Using External tables with internal (entity) tables

Good morning, 

We have a read-only table from a legacy system that we have added to Outsystems as an extension.      Accessing the data works very well, but we are hitting a bit of a roadblock;  we are trying  create an aggregate to pull back data from an internal table where the username = the username from the external table.  

Since OS knows the second table is external it will not allow this type of behavior.   I'm sure others have had similar use cases, but I'm not sure what the proper work around is for this.   Any suggestions?



Solution

Hi,


So you are trying to join tables that are on two different servers. This is not really an OS issue, more a database issue.  You have a few possible solutions.  Create a sync process to replicate external data into the platform.   Alternatively, you could do two queries. One to get the first username and the 2nd to use that and do the query.


Solution

Hi Josh,

If I understand you correctly, you want to do a JOIN query on an external entity with an "internal" entity.

So, a JOIN across servers.

There are at least 2 ways to do this.

The first way is with stored procedures, like here:

https://stackoverflow.com/questions/8752477/joining-tables-from-different-servers

Then use a SQL query to call the stored procedure.

The other is to use a view instead of a stored procedure.

This process is done much the same, you'll still need to setup the connection, but instead of creating a stored procedure, you'll create a view, with the SQL to join both tables.

Then, using Integration Studio, simply add the view as an external entity.

Performance warning: joining tables across servers is generally a not a great idea, because, as you can guess, all of the necessary data in the remote server (the one where the table is) will have to be sent to the local server (the one running the query), and that can be pretty slow, depending on your infrastructure. 


Stacey Levine wrote:

Hi,


So you are trying to join tables that are on two different servers. This is not really an OS issue, more a database issue.  You have a few possible solutions.  Create a sync process to replicate external data into the platform.   Alternatively, you could do two queries. One to get the first username and the 2nd to use that and do the query.



That's what we ended up doing, creating two aggregates to pull back the required data.