function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Admin_SF_TPAdmin_SF_TP 

SOQL - filtering whole subquery to be not null

Hello,

I'm trying to filter a SOQL subquery (in the SELECT section) to be not null, but I hadn't had any luck so far.
The query:
SELECT (SELECT AccountId, ActivityDate, CreatedDate, CreatedById FROM OpenActivities WHERE ActivityDate = LAST_N_WEEKS:8 Order by ActivityDate desc) FROM Account

The query is showing some OpenActivies results, but far more null results that I don't need

Any ideas ?

Results

 
Best Answer chosen by Admin_SF_TP
Head In CloudHead In Cloud
You can use a workaround for this like this: 
list<account> newlist = new list<Account>();
For(Account acc : [SELECT (SELECT AccountId, ActivityDate, CreatedDate, CreatedById FROM OpenActivities WHERE ActivityDate = LAST_N_WEEKS:8 Order by ActivityDate desc) FROM Account]){
  if(acc.OpenActivities.size() >0){
      newlist.add(acc);
  }
}

In the newlist, you will get the desired results.

Please mark as solved if it helps. Thanks

All Answers

Head In CloudHead In Cloud
Hi, you can try querying on the child object here like this: 
SELECT AccountId, Account.name, ActivityDate, CreatedDate, CreatedById FROM OpenActivities WHERE ActivityDate = LAST_N_WEEKS:8 Order by ActivityDate desc

This will solve your problem.
Admin_SF_TPAdmin_SF_TP
Thank you for the answer!

When I started, I tried to execute the query not as a subquery, but the Force.com Explorer gave me an error, that the sObject type 'OpenActivities' is not supported. That's why I nested it as a subquery.
Head In CloudHead In Cloud
You can use a workaround for this like this: 
list<account> newlist = new list<Account>();
For(Account acc : [SELECT (SELECT AccountId, ActivityDate, CreatedDate, CreatedById FROM OpenActivities WHERE ActivityDate = LAST_N_WEEKS:8 Order by ActivityDate desc) FROM Account]){
  if(acc.OpenActivities.size() >0){
      newlist.add(acc);
  }
}

In the newlist, you will get the desired results.

Please mark as solved if it helps. Thanks
This was selected as the best answer
Admin_SF_TPAdmin_SF_TP

Yeah, that's kind the solution that I'm using right now. I was just wondering, if there was a way to filter out the Accounts, which doesn't have Open Activities, on SOQL-Level.

Getting the whole list and filter it in Java isn't the problem, but if there isn't a way doing it just with SOQL, I'll mark the workaround as solution.