2

I am not able to make this query work, I don't know how to use the WHERE NOT IN clause. Can anyone please help me with this? I will appreciate any help. Thank you!

First approach:

 existingTasks = [Select Subject From Task Where WhatId IN :  existingServTaskLibrary  ];

 for(Service_Task_Library__c ta: [SELECT Id, Name  from 
   Service_Task_Library__c  WHERE Service__c IN : selectedServices  And 
   Subject__c NOT IN: existingTasks  Order by Service__c ASC]) 
   {
     .. do something

    } 

Error first approach

Invalid bind expression type of Task for column of type String

Second Approach

 for(Service_Task_Library__c ta: [SELECT Id, Name  from 
   Service_Task_Library__c  WHERE Service__c IN : selectedServices  And 
   Subject__c NOT IN (Select Subject From Task Where WhatId IN : existingServTaskLibrary) 
   Order by Service__c ASC]) 
   {
     .. do something

    } 

Error for second approach:

Error: Entity 'Task' is not supported for semi join inner selects

1

Mistake in First approach: Here you are using existingTasks which i assume list of task. When you use this as Subject__c not in : existingTasks, Soql compiler thinks existingTasks as list of String but it is list of Task. So only you got the error.

Suggestion: After existingTasks query add this.

Set<string> subjectSet = new Set<string>();
for(Task t : existingTasks){
    subjectSet.add(t.Subject);
}

Use the new Set in for each loop as

Subject__c NOT IN: subjectSet

If you succeed in this, there won't be a need for second approach ;)

  • Vigneshwaran-g, thank you so much! I used your approach and it works :) Have a great week. – Carlos Jun 1 '15 at 14:48
3

I think pulling out the Task part of the query won't affect the efficiency of your existing for(...[Select]) statement.

I'd just query your subject list prior to the for loop:

Task[] tasks = [Select Subject From Task Where WhatId IN :existingServTaskLibrary];

Then you can just include this tasks variable in your NOT IN clause.


EDIT

Oh and yes, I didn't look at the first approach - and the fix suggested by Vigneshwaran G is correct - you need a list of ids, not a list of sObjects.

One improvement would be to enclose that query in a Map to extract the ids without a loop - ie:

Map<Id,Task> taskMap = new Map<Id,Task>([Your query here]);
Set<Id> taskSet = taskMap.keySet();

Or even faster:

Set<Id> taskSet = new Map<Id,Task>([Your query here]).keySet();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.