Hi,
using service studio 11 on the outsystems cloud (free) :)
I have an aggregate function with two groups and two sums.
The output is not respecting the group by clause nor the order by clause.
This is the output
The aggregate group and sum
This is the sorting on the aggregate
This is what is build the platform
The SQL
It should be group by logdate, domain and not domain,logdate
I know that it would be simpler to write the SQL function, but I want (would like) to write the minimum code as possible, it is my own challenge.
Thank you for your time is reading this long story, few words many pictures :)
Thank you
Hi Carl,
You have to use sorting on the calculated date (groupby Data) and table.domain.
To simplify the statement, please see the below illustration:
The dates in the table (Sheet1) is not in order
Now when the data is aggregated and grouped:
you need to sort on
where LogDate is the computed Group By Attribure and Sheet1.Domain is the table attribute.
Hope this helps,
Regards,
Saugat
Sorry first answer I didn't read something correctly I'll try again.
Group by doesn't respect any particular order. It just puts one line per unique combination so having Domain, Log_Date or Log_date, Domain will give the same result. It will simply add everything up that the two match exactly. Looking at you example it appears to be grouping correctly to me. Can you give an example of what you are expecting as the output.
This is normal SQL behavior and isn't specific to Outsystems, you will get the exact same result if you wrote it directly in SQL
Also for the sort you need to sort by the grouped by columns not the native column. The Group by will have created 2 new fields called just Domain and Log_Date so in the sort you need to select these instead of ByLogDate.Log_Date and ByLogDate.Domain ie just Log_Date not ByLogDate.Log_Date.
Where you are getting confused is that Outsystems by default uses the same name for the grouped field as the original field just without the table name.
Like Suagat said, you have to use grouped attribute in sorting list instead of the entity attribute if you want to order based on groupd values. There is just one correction in what Suagat as suggested that is for Domain also you should choose grouped attribute as you have used group by domain also in your aggrigate.
Hi all,
many thanks to all, but specially to Saugat, it did work as you said.
I changed the order column to the one without the table name.
Here is the code in oracle with the expected result, just for curiosity
Once again, thanks to everybody who had the time and patience to read.
Carl