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!