+ Start a Discussion

non-selective SOQL

hello helpers

i am experiencing a strange error when try to run an soql query within a trigger.

the query try to retrieve a record set from Attachment object and look like

String variable ='something';
list<Attachment> att =[select id, Name from Attachment where Name =:variable ];

the attachment object contain more then150000 records.

the query throw an exception saying that is non selectiv.

when I execute the same query in developer console I there is no attacment having that name.

so my question is how could be non selectiv a query which would return zero records?

thanks in advance
You need to use the dot notation selector for the field "Name" on the attachment object.

String variable ='something';
list<Attachment> att =[select id, Name from Attachment where Name =:variable.Name ];

See: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_attachment.htm
Abhishek BansalAbhishek Bansal

Below is the reason mentioned for Non-Selective Query :
  • When your object holds a large number of records (As in your case) and you query on that object by using a filter which is not an indexed field than you will get the error as you are recieving.
  • In your case, your object is holding too many records and you are using a filter on Name field which is not considered to be indexed so you are recieving the Non-Selective query error.
Please have a look on the below link to know more about Non-Selective queries and how to overcome them :
https://help.salesforce.com/apex/HTViewSolution?urlname=How-to-make-my-SOQL-query-selective&language=en_US (https://help.salesforce.com/apex/HTViewSolution?urlname=How-to-make-my-SOQL-query-selective&language=en_US)

Let me know if you need more clarification or information on this.

Abhishek Bansal.
Hello Abhishek

as I know Name  field  is  by default indexed  for all SF  objects

Abhishek BansalAbhishek Bansal
Yes you are right.
Standard name fields are indexed but your query can be non-selective even if your filter is indexed.
Please see the information on the link provided above.
May be it is useful to you.

Hello CloudNerd

The syntax you suggested  is  not  accepted
when I chnage  list<Attachment> att = [select Id,Name From Attachment where Name = :attName];

list<Attachment> att = [select Id,Name From Attachment where Name = :attName.Name];  I got a compile error lie below

Error: Compile Error: Initial term of field expression must be a concrete SObject: String at line 348 column 126