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
Michael AllenMichael Allen 

Doing a 'nested' AND query in SOQL

Hi,

I have filtered 'Related Lists' (actually Apex Tables) on Account. I need to do a 'nested' AND statement.

Query is as follows: 
SELECT Id, Subject, LastModifiedDate FROM Task WHERE isClosed = FALSE and (NOT Subject LIKE'%Email: %') and OwnerId != '00520000002DBP2' ORDER BY ActivityDate DESC NULLS LAST LIMIT100];
I want to show all open Tasks, except Tasks owned by the specified user that have a Subject starting with 'Email: '

The problem is it excludes all Tasks owned by the User AND all Tasks where Subject starts with 'Email: '.
I want to do something akin to this: 
SELECT Id, Subject, LastModifiedDate FROM Task WHERE isClosed = FALSE and
( (NOT Subject LIKE '%Email: %') and OwnerId != '00520000002DBP2' ) ORDER BY ActivityDate DESC NULLS LAST LIMIT 100];
...but can't find out any information on how to do it syntactically. Do I need to nest a whole other query in it?
Any help would be greatly appreciated.

Thanks
Shyama B SShyama B S
Hi Michael,

Please try this and let me know if it works 
SELECT Id, Subject, LastModifiedDate FROM Task WHERE isClosed=FALSE AND (Not (Subject LIKE '%Email: %' AND OwnerId = '00520000002DBP2')) ORDER BY ActivityDate DESC NULLS LAST LIMIT 100

thanks
Shyama
 
Michael AllenMichael Allen
Hi Shyama,

Thanks for this. I like your thinking - I didn't think of it that way. But sadly it's throwing back:

Error: Compile Error: expecting right square bracket, found '(' at line 47 column 500

This is the first bracket. Shown in bold.

....WHERE isClosed=FALSE AND (Not (Subject....

I mentioned earlier that this query builds a 'filtered related list'. The full query is as follows. I had deliberately kept it slim last time for ease.  
leadtaskhistory=[Select t.Who.Type,t.What.Type,  t.WhoId, t.WhatId, t.Type, t.Task_Type__c, t.SystemModstamp, t.Subject, t.Status, t.ReminderDateTime,  t.Rating__c, t.Priority, t.OwnerId, t.LastModifiedDate, t.LastModifiedById, t.IsClosed, t.Id, t.Description, t.Date_of_Booking__c, t.DB_Activity_Type__c, t.CurrencyIsoCode, t.CreatedDate, t.CreatedById, t.Comment_Summary__c, t.CallType, t.CallObject,  t.ActivityDate, t.AccountId From Task t Where Isclosed = True and OwnerId = '00520000002DBP2' and (NOT Subject LIKE '%Email:%') and WhoId in: relatedLeadIds order by ActivityDate DESC NULLS LAST LIMIT 100];
I'm drawing a blank on this one I'm afraid. I'm really not sure how to proceed...

Thanks
 
Shyama B SShyama B S
Hi Michael,

I tried to use the query in an apex class (removing the custom task fields from your query) and it looks something like this with no compile errors:

User-added image
This is the code: 
public class TskTest {
    List<Contact> contactList=new List<contact>();
   
   List<Task> task1List =[Select t.Who.Type,t.What.Type,  t.WhoId, t.WhatId, t.Type , t.SystemModstamp, 
                          t.Subject, t.Status, t.ReminderDateTime, t.Priority, t.OwnerId, t.LastModifiedDate, 
                          t.LastModifiedById, t.IsClosed, t.Id, t.Description, t.CreatedDate, 
                          t.CreatedById,  t.CallType, t.CallObject,  
                          t.ActivityDate, t.AccountId From Task t Where Isclosed = True and 
                          (Not (Subject LIKE '%Email: %' AND OwnerId = '00520000002DBP2'))  and WhoId in: ContactList 
                          order by ActivityDate DESC NULLS 
                          LAST LIMIT 100];
}

Hope this helps. Good luck.

Thanks.