How can modify the sql after joining all the entities. As I would like to select Max/Min for the field in order to make my report, seems the executed SQL in the aggregate cannot be edited.
Do I need to add dataaction in order to make my own report by SQL query?? Please guide me the approach. Thannks.
Hi Winnie,
The "executed SQL" is just an information screen, it shows what SQL the Platform sends to the database, based on the Aggregate you created. Therefore, you cannot edit it.
If you want to create a more complex SQL (e.g. with CTEs, or subqueries, etc.), you'll need to use a Data Action and add a SQL query to it.
However, if you need a Min or Max on a column, an Aggregate can easily do that. Just click on the column header, and select "Min" or "Max":
Hi Winnie
Actually after joining all the entities . there is an inbuilt program you can able to see in this link -Built-in Functions - OutSystems 11 Documentation .
You mean below function in the filter?
But What I want is getting the latest status record where status =completed only for each Job, there are many job tasks for one Job.
So I want to use some query to extract the value, seems the build in function is not enough?
For example :
JobID, Job TasksID, Phase Code, Target Date, Completion Date, Status
1, 1, 10001, 2023-08-01, 2023-08-02, Completed
1, 2, 10002, 2023-08-02, 2023-08-03, Completed
1, 3, 10003, 2023-08-03, 2023-08-04, Pending
2, 1, 10001, 2023-08-02, 2023-08-02, Completed
2, 2, 10002, 2023-08-03, 2023-08-03, Completed
What I want for the result from the table is
Can you help with me? Thanks.
Like you can use the filter option . according to your query
Would you mind to provide an example for me on the filter to extract the latest record. Which build in function I should use?