Hi,
I am working on a real time dashboard. where in local DB user will input some data. I need to Join with external DB table and show value. since I can't join two different DB table, I am seeking suggestions, how I need to proceed?
Is it workable method, pull records at least every 5 mins or less, in to local table and then join with local and show in dashboard?
Hi @Raghavendra Tantry Lakshminarayana
Please check the below links:https://www.outsystems.com/forums/discussion/61543/how-to-join-internal-user-entity-and-external-database-in-advanced-sql/#
https://www.outsystems.com/forums/discussion/49893/using-external-tables-with-internal-entity-tables/#
https://www.outsystems.com/forums/discussion/23210/join-of-internal-and-external-database-tables/#
RegardsKrishnanand Pathak
The implementation i did before was to have a clone of the external tables inside of outsystems and have a sync action using a Timer or ondemand (it depends on your requirements). That way you can use them in a single aggregate
Another way is you can use CTE. you convert the list you get from the external db to JSON and use the JSON in an advancedSQL in a CTE
Thanks for your reply.
correct me if i am wrong. I create reactive web app. as i seen sync option is there only for mobile application.
external tables are from oracle. not straight forward as MSSQL my bad luck.
Hello Raghavendra Tantry Lakshminarayana
Create an Integration in Integration Studio that connects to the external database table you want to join with.
In the Data Actions, create an action that retrieves the data from the local database table and an action that retrieves the data from the external database table.
Combine the data retrieved from both databases by mapping the relevant fields to a new structure or entity. You can use a ListAppend or ListAppendAll action to combine the records retrieved from both tables.
Finally, bind the resulting data to the widgets in your real-time dashboard.
ETL tools like Apache NiFi, Talend, or custom scripts can help you extract data from the external database, transform it if necessary, and load it into your local database. These tools often provide a user-friendly interface for designing data flows and handling complex transformations. Configure your ETL tool to connect to the external database and extract the desired top data enrichment companies. This may involve specifying the tables or queries to extract data from, defining any necessary transformations, and setting up a schedule for periodic extraction. If the data needs to be transformed before loading it into your local database, you can use the ETL tool to perform these transformations. This might include data cleansing, joining multiple data sources, or converting data types. Once the data has been extracted and transformed, the ETL tool can load it into your local database. This typically involves connecting to the local database and specifying the tables or schema where the data should be loaded. CDC tools like Debezium or custom scripts can capture changes (inserts, updates, deletes) from the external database's transaction logs and apply them to your local database. This ensures that your local database stays in sync with the external database, even as data changes over time.