Hi Guys,

I'm trying to use Static Entities with Entities exposed in Extension but I'm not able to join the two since it becomes a cross database join and isn't supported by OS. 

Have you experienced this before if yes, how did you resolve it.


Edit: I know I can add the Static Entity in 3rd party database I'm importing through extension but that's not the path we want to take since we don't have control over the 3rd party database.


Thanks in advance

OutSystems does not recommend using joins over linked servers. 

https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Queries

From the OutSystems documentation. 

Inner joins with foreign entities tend to result in slow queries. However, if you must make a join over a linked server, make sure to use additional logic so that only a data subset is pulled from the linked server and only what was pulled will be joined (temporary tables, OPENQUERY, ...). Whatever the case, joins over linked servers need to be addressed very carefully.

Amal Raj wrote:

OutSystems does not recommend using joins over linked servers. 

https://success.outsystems.com/Documentation/Best_Practices/Performance_Best_Practices/Performance_Best_Practices_-_Queries

From the OutSystems documentation. 

Inner joins with foreign entities tend to result in slow queries. However, if you must make a join over a linked server, make sure to use additional logic so that only a data subset is pulled from the linked server and only what was pulled will be joined (temporary tables, OPENQUERY, ...). Whatever the case, joins over linked servers need to be addressed very carefully.


Hi Amal,

OS is not even allowing to join the two:

I don't even get the Static Entity I created as an option to join even after putting it in Sources.



Hi Sunil,

That was never supported by Outsystems. You can only join tables that use the same database connection which is not your case.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi Sunil,

That was never supported by Outsystems. You can only join tables that use the same database connection which is not your case.

Regards,

Marcelo

Any way to work around it? Or what sort of a solution would you recommend in such situation? 


Hi Sunil,

If you the list you get from external database is small you can iterate through it and fill the data missing that is only on Outsystems side (this is possible since you said is only a static  table). Other option would be bulk insert everything in a staging table in your side and query it with the joins you want.

Regards,

Marcelo