Making a SubQuery with a Aggregate

Making a SubQuery with a Aggregate

  
Its possible with the aggregates have the some funcionality of a SubQuery in a SQL ?

TIA
Hi Alberto,

Unfortunately in that case, it's not possible to have the full expressiveness of SQL in an Aggregate. In order to achieve a similar result, you would first have to create an Aggregate to be used as the subquery, and create another Aggregate that uses the first one's results in some way, e.g., a filter.

What was the query you were trying to create?

Best regards,
Sérgio
I have already tried without full success!
Let's show my idea:


1st Entitie (1st Aggregate)
ID    Atr01    Atr02
1    1    T
2    2    F
3    2    F
4    4    T
5    1    F
6    6    T

Filter Atr02=True (or better Atr02 only)

2nd Entitie (2nd Aggregate)
ID    Atr01    
1    R1
2    R2
3    R3
4    R4
5    R5
6    R6

I want filter that 2nd Agregate with something like

 (first).Atr01=(second).ID

And get only the 1,4,6 Record of the second Entitie


Thanks again




Hi Alberto,

If I understood correctly, I think you can achieve the same result by having a single Aggregate with both entities, joined with the condition "First.Atr01 = Second.ID", and the filter "First.Atr02 = True".

Regards,
Paulo Ferreira
Thanks Paulo

You are right. With my example we can achived the result. But the exposed example is limited. Imagine that the first  entitie didn't have 6 records but 8 or more.... something like

1st Entitie (1st Aggregate)
ID    Atr01    Atr02
1    1    T
2    2    F
3    2    F
4    4    T
5    1    F
6    6    T

7    4    T
8    1    T
....


With that I will not only 3 records (1,4,6 ) of the second entitie but at leat 5 (all that Atri01 is T).

in that case what to do?

Thanks Again
I'm not sure I understood correctly. That is the same scenario as the previous one, only with more records, correct?

Regards,
Paulo Ferreira
Hi Alberto, Are you concerned about getting the same result multiple times, is that it? Cheers Daniel
Thanks for you atention Daniel.

To be clear, what I am trying to achived is to remake the Booking exercise (in forge) without using SQL but insted using Aggregates. To verify if we can do the same with it.

The question is in the webscreen Room List where is used the SQL (with a subQuery)  to get the available Rooms to booking.

Until now I couldn't get the same result. But I not give up of try.

I will be grateful if someone helps me!! :)

This is the SQL that I want to do with aggregates

SELECT{Room}.*
FROM{Room}
WHERE @NumberofAdults > 0 and
    {Room}.[AdultsCapacity] >= @NumberofAdults and
    {Room}.[AdultsCapacity] +{Room}.[ChildrenCapacity] >= @NumberofAdults +
        @NumberOfChildren and
    not exists
        (SELECT 1
        FROM{Booking}
        WHERE{Booking}.[RoomId] ={Room}.[Id] and
            (@CheckInDate between{Booking}.[CheckIn] and dateadd(d,-1,{Booking}.[CheckOut]) or
            @CheckOutDate between dateadd(d,1,{Booking}.[CheckIn]) and
            {Booking}.[CheckOut]) and
            {Booking}.[StatusId] <> @CanceledStatus)
Hi Alberto,

It seems to me that you could join Room and Booking in your Aggregate, on Booking.RoomId = Room.Id, and then filter by all the conditions at once (the ones for Room and the ones for Booking).

I haven't tried it though, so I could be wrong. Give it a shot.

Cheers,
Daniel
Hi Alberto,

You will have to use the SQL for this one since it uses a subquery. Can you point me to the booking exercise in the forge?

Thanks.