Hi all,Thankyou all in advance for your your valuable feedback!I recently faces an error showing "The Aggregate cannot join data from multiple databases"I was trying to use an aggregate where i have taken one normal table and one other table that is from an external database.I have checked few similar posts on forum, but still I wanted to discuss this in depth to have more idea about this.
With all of your help and replies I can understand this better.
Thankyou!
Hello Ayushi,
Hope you're doing well.
When you use an Aggregate in Service Studio, the platform automatically generates a SQL query behind the scenes. However SQL queries cannot be executed across two separate physical databases, that's why you're getting this error.
There are some possible workarounds for your use case, for example:
1) Create two separate Aggregates, one to fetch the data from the external database and another to fetch the data from the OutSystems database. After that you can use logic to join the results.
2) Create a staging / synchronization table in the OutSystems database to store the data from the external database. Basically you'll have faster queries, but you'll end up with duplicated data and you have to sync it if there are changes in the source system.
Let me know if you have any additional questions.
Kind regards,
Rui Barradas
Hi Ayushi,
In OutSystems, it’s not possible to directly join two entities from different data sources. Confirming what Rui mentioned, you can use an approach like:
Sync Data Using a Timer (Batch Job)