I want to bring all the records that are not in an entity

Is it possible to do a "Not in" or "Not Exists" in an aggregate? Plz help i'm a begginer in Outsystems


You'll have to use a SQL query to do that, instead of an aggregate. Your SQL would look something like this:

SELECT * FROM {YourEntity} WHERE {YourEntity}.[Id] NOT IN (SELECT Id FROM {OtherEntity})

Leidy Navarro wrote:

Is it possible to do a "Not in" or "Not Exists" in an aggregate? Plz help i'm a begginer in Outsystems


Leidy,

You know NOT EXISTS, LEFT JOIN / IS NULL are semantically equivalent, right? So you have this options to use in aggregate. 

But I think in this case it´s more eficiently use Advanced SQL.

You can take a look in this other post

https://www.outsystems.com/forums/discussion/14219/using-the-in-clause-in-an-aggregate/


SyntaxEditor Code Snippet
This is my sql query and no matter what I do I always bring all the records of "Strategy", I only need the records that are not in the entity "StrategiesMeta"

SELECT * FROM {Estrategia} WHERE {Estrategia}.[estado]=1 and {Estrategia}.[Id] NOT IN (SELECT {EstrategiasMetas}.[Id] FROM {EstrategiasMetas} where {EstrategiasMetas}.[MetaNegocioId] =@MetaNegocioId)

Is the "Strategy" id, present in the "StrategiesMeta" entity, really called "Id"? Isn't it called something like "StrategyId"?

Aurelio Santos wrote:

Is the "Strategy" id, present in the "StrategiesMeta" entity, really called "Id"? Isn't it called something like "StrategyId"?

That's right!!! Thank You so much!!! 


Leidy Navarro wrote:

Aurelio Santos wrote:

Is the "Strategy" id, present in the "StrategiesMeta" entity, really called "Id"? Isn't it called something like "StrategyId"?

That's right!!! Thank You so much!!! 


Hello Leidy ,

Did you get any solution for this problem?

I am facing same issue. Any pointer will help.

Thanks and Regards

Prajakta Roshankhede


Hi Prajakta,

Like CM_Techy wrote above, you can use a left join and a test for null. To do this in an Aggregate (assuming you want all A records that are not referenced from B):

  • join B to A with a "With or Without";
  • add a Filter that says "B.Id = NullIdentifier()"


Kilian Hekhuis wrote:

Hi Prajakta,

Like CM_Techy wrote above, you can use a left join and a test for null. To do this in an Aggregate (assuming you want all A records that are not referenced from B):

  • join B to A with a "With or Without";
  • add a Filter that says "B.Id = NullIdentifier()"



Thank you :)

You're most welcome :) Happy coding!

Kilian Hekhuis wrote:

You're most welcome :) Happy coding!

Hi sir I have an almost same scenario to this kind of problem but in my case I have an additional entry date and time and exit date and time input. I need all the list in Entity A not existing in the Entity B and All that list must be within the range of entry datetime and exit datetime which  does not exist in Entity B.


I am already able to get the all the list of entity A not existing in Entity B I just have a problem in the part of entry datetime and exit datetime, hope you can answer my question....