Hello Jessica.

An Aggregate or Advanced SQL, is nothing more than a SQL Query in disguise.

You can't do it without linking the servers.

Please look at this old thread. It is still valid.

https://www.outsystems.com/forums/discussion/10253/query-cannot-be-simultaneously-using-entities-and-the-following-foreign-entities/

Hi Jessica,

Please bear in mind that cross server joins are very inefficient and they should be avoided at all costs. 

They are not considered a Performance Best Practice.

Regards,

Nordin

Thank all,

As my outsystem platform is on cloud, the service center doesn't have the 'Database Catalog' to configure. Please advise.

You need to set a VPN to connect cloud to internal servers.

Yes, it is not advisable (Best Practices has MANY warnings against it).

We have VPN connect between outsystem cloud and internal servers.

Then, what steps I should follow to join data from user entity (internal DB) and oracle 

Cross server joins are very inefficient because the table in the linked server is completely loaded to the local DB server and then the join is performed. Cross server joins may be acceptable if the tables are small and unavoidable but as a rule cross server joins should be avoided as much as possible.

In: https://success.outsystems.com/Documentation/Best_Practices/Performance/Performance_Top_10_Rules

It will depend on the type of join.

If you only need to filter, I would save the ids into a string and apply it as inline parameters.

If you need columns from it, I think performance-wise the best approach is to:

1. Filter the external table to the maximum
2. Save it into a Common Table Expression or Temporary Table (if possible, it is unlikely that this will be a possibility)
3. Do your query.

The most probable scenario is:

1. Create a view over the linked server table. It will be used a lot.
2. Do your join
3. Wait

Thank Nuno. 

May I clarify more?

2. Do a join. You mean to do the program logic myself

Actually, I change my mindset to SQL and forgot OS. You have to do it differently.

This is for a DBA:

• On the SQL that has OutSystems create a linked server to Oracle.
• Also on the SQL, create a view to join OS table and Oracle Table This will be huge and unfiltered. That's the issue.

And this is for OS developer:

• With an Extension you consume the view in OutSystems as if it is external.
• Apply filters on the new read-only Entity.
• If you need to do updates, you can use the same id from read-only Entity in the OS native Entity.

It sounds confusing and you may need more time to understand becasue it really isn't that common to use.

The normal would be to fetch a single record from Id. If you need the full table, you should import it to OS.