Union in Aggregate

Union in Aggregate

  

Hi,

I have SQL written in below in SQL Widget.How should i do it in Aggregate?

--open by me
SELECT {SRJournal}.* ,
{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],
{SRStatus}.[Label],{User}.[Name],Pickup.[Name]

from {SRJournal}
inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]
and {SRStatus}.[Label] in ('Pending Approval','Request For Info')
inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]
inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]
inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]
inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]
where {SRJournal}.[ModifiedBy]=@PUserId

union
----Pending my approval
SELECT {SRJournal}.* ,
{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],
{SRStatus}.[Label],{User}.[Name],Pickup.[Name]
from {SRJournal}


inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]
and {SRStatus}.[Label] in ('Pending Approval')
inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]
inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]
inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]
inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]
where  {SRJournal}.[ApproverId]=@PUserId

union
-------pending for my process
SELECT {SRJournal}.* ,
{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],
{SRStatus}.[Label],{User}.[Name],Pickup.[Name]
from {SRJournal}

inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]
and {SRStatus}.[Label] in ('Approved','In Process','Info Ready')
inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]
inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]
inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]
inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]
where {SRJournal}.[EntityGroupId] in (

select {SRUserProfileDtl}.[EntityGroupId]
from  {SRUserProfileDtl} where {SRUserProfileDtl}.[UserId]=@PUserId
)


Help please !!



Aggregates don't support the UNION operator nor the IN operator. You should use the AdvancedSQL widget instead.

João Pedro Abreu wrote:

Aggregates don't support the UNION operator nor the IN operator. You should use the AdvancedSQL widget instead.

Hi João,

All the SELECTs are fetching data from the same entity, with the same joins, with the exception of the SRStatus (that he is doing through label...).

This one should be pretty easy to do with an aggregate...

No?

Cheers,
Eduardo Jauch

Yes, using a union for this is nonsense, even if using SQL. Also, since the IN is just a few values, OR could be used as well, but the most worrisome is the check on Label. One should never ever ever check on a label. If the table is defined in Service Studio, it should be a Static Entity, and in the Aggregate you should use Entities.SRStatus.Attribute. If it isn't a Static Entity, and you really can't make it into one, you should at least have some code, instead of a label.

Hi Eduardo, can you guys share some simple example?

Hi Johnson,

An example of what? Aggregates are pretty basic Outsystems Platform stuff, thoroughly explained in the training videos. We love to help people that are stuck, but it seems you are not as much stuck as trying to use the Platform without the proper preperation. We're problem solvers, not unpaid teachers!

Solution

Hi Johnson,

Based on your query I wrote an article, which you can find here: https://itnext.io/outsystems-101-aggregates-vs-sql-queries-ec7223f4c496.

In this article I point out which steps you need to take in order to create an aggregate from your SQL.

Hope this explanation helps you to learn the OutSystems platform.

Kind regards,

Remco Dekkinga

Solution

Very good article Remco. 

Remco Dekkinga wrote:

Hi Johnson,

Based on your query I wrote an article, which you can find here: https://itnext.io/outsystems-101-aggregates-vs-sql-queries-ec7223f4c496.

In this article I point out which steps you need to take in order to create an aggregate from your SQL.

Hope this explanation helps you to learn the OutSystems platform.

Kind regards,

Remco Dekkinga


Good article but you are forgetting that a Union removes duplicates and ListAppendAll does not.
And there is no way a ListAppendAll makes a Union obsolete, not meationing that you get a better performance running 1 Advanced with N Unions then N Agregates with N-1 ListAppendAll

Luís,

I'm sure Remco is aware of what you're saying, and I don't think he implies anywhere that a ListAppendAll is always a good replacement for a UNION.

It doesn't matter for the OP though, as the four queries are mutually exclusive (and can be written as a single Aggregate).

Kind of does though


But you are right, the four queries in the OP can be writen in a single Aggregate


Hi Luis,

I wrote that it can be done with a ListAppendAll and not that this is a best practice in all cases. 

I do see very often that people go for the SQL query when they need just one union and they want to build that query quick and dirty without thinking about optimization and including all kinds of entities in the output. I wrote the article to show other possibilities for those people.

@kilian, thanks!