Hello there,I have a complex aggregate for my screen, and I want to replace it with data action and use SQL widget.I attached details of my aggregate and the structure of my data action/SQL. Could any one help with the SQL widget?Thank you inadvance for your help.
Hello.
Here is some general advice on how to proceed:1. Input Parameters: Identify any filters that your aggregate might be using with external values and create Input Parameters for them in your SQL.
2. Query Construction: All your aggregate joins could be represented as JOINs in SQL, and your aggregate filters can be WHERE statements in SQL. "With or Without" is "Left Join". "Only With" is "Inner Join".
3. Return Data: The SQL widget returns a List of a certain structure, so make sure to define your structure as output parameter for the widget.Please, keep in mind, the SQL Widget should only be used when the aggregate cannot solve your use case or if you need to optimize a complex query. OutSystems aggregates abstract away concepts such as denormalization and protect against injection attacks. Hence, ensure the SQL queries are correctly optimized and secure.
Hello Nuno, Thanks for sharing your knowledge
Hi @Fatemeh Safinia ,
if it is because of a lack of SQL knowledge, here's a tip to help you come up with the SQL : execute the aggregate in service studio, and then look at the generated SQL. This will be with table names and column names instead of entity names and attribute names, but it will help you construct your own SQL.
Dorine
Hello Dorine,That was a really good one, thanks
hallo @Fatemeh Safinia ,
I think you need to go through the conversion process step by step:
Retrieve all the necessary tables using INNER JOIN or LEFT JOIN, depending on your business logic.
Define the required input parameters and implement the conditional logic based on your specific requirements.
Don’t forget to include sorting, as it may impact the usability or correctness of the result.
Make sure to apply record limits (e.g., maximum number of records to return) to ensure good performance, especially with large datasets.
Start by creating your DTO (Data Transfer Object) and select only the specific columns you need to return, to match your structure and improve performanceIf possible, share a simple OML file — this will help us better understand your scenario and provide a more accurate and helpful solution.