Dear All,
I work with external database. On my case I should get same data from two table and groupped some column.
I try to use Aggregate and use join beetwen two table with = condition and it's normally.
But when I try to Publish my module, I have an error
In the query '<SQL>' the Foreign Entities are located in different databases: '<Foreign Entity 1>', '<Foreign Entity 2>',… in '<Database Connection A>'; '<Foreign Entity 3>', '<Foreign Entity 4>',… in '<Database Connection B>'
Yap, that tables is located in a different database.
My question:1. Can I solve my problem with case like I write on top and how?
2. Is there any other way to solve this problem?
Regards,
Kane
Hi
Have you tried creating manual query using SQL Widget ?
I think the problem happened because the foreign key must exist on the database, but you use external database.
Thanks
Hi,
Joining from SQL Widget
Just tested it. If you want to joining from Outsystems generated entity with external databases, it will shown error like on aggregate.
I think you can if both are external tables though.
My question is joining table from other database and both table use external database.
I have tried with aggregate and SQL and show error like I mention.
Does both table come from the same external databases ?
I manage to do this and didn't have error.
if both of your tables are external in different databases on the SQL server you could also make use of a View which combines both tables. Then, instead of using the tables in integration studio, you add the view.
Here is an example of creating a view on SQL Server
CREATE VIEW MyView
AS
SELECT
c.Id,
c.FirstName,
c.LastName,
d.Amount
FROM [Database1].dbo.Contact c
INNER JOIN [Database2].dbo.Detail d ON d.Contact = c.Id
Best,
Stefan
My bad, Logical database is very important. I check both table is come from different database and different logical database.
After I change both table to same logical database it's showing normally on Aggregate and SQL Widget. Thanks.
Hi Kane,
This seems to make sense. If you use an aggregate with a join, OutSystems sends this as an SQL Command to the database server. The database server then executes the query and sends back the result. Since you're using two databases, there's no way to execute the join tables from another database. If the SQL is executed on database 1, it has no knowledge of the tables in database 2.
That's my problem, I don't know how to reproduce this issue
Hi Kany ,
Before you try to put the join between two database servers please go through some of the best practices to use SQL queries in their best way.
https://success.outsystems.com/Documentation/Best_Practices/Performance_and_Monitoring/Performance_Best_Practices_-_Queries
-PJ-