+ Start a Discussion
Dev_AryaDev_Arya 

why this query with And clause in Where fails?

hi all,

the soql in the following code is failing. Could somebody explain this to me.
Map<Id, String> profileIds = new Map<Id, String>();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE NOT(Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet()];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}
Where i remove one of the clause from the where statement, it works.

Thanks.
 
Best Answer chosen by Dev_Arya
v varaprasadv varaprasad
H dev,

Please check below code : 
 
Map<Id, String> profileIds = new Map<Id, String>();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet()];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}

Hope this Helps you.

Thanks
Varaprasad

All Answers

v varaprasadv varaprasad
H dev,

Please check below code : 
 
Map<Id, String> profileIds = new Map<Id, String>();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet()];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}

Hope this Helps you.

Thanks
Varaprasad
This was selected as the best answer
Amit Chaudhary 8Amit Chaudhary 8

Try to update your query like below
SELECT Id, Name, Email, ProfileId FROM User WHERE ( NOT(Email like '%@example.com') ) AND (ProfileId NOT IN :profileIds.keySet() ) limit 1

Update your code like below
Map<Id, String> profileIds = new Map<Id, String>();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE ( NOT(Email like '%@example.com') ) AND (ProfileId NOT IN :profileIds.keySet() ) ];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}

Let us know if this will help you

 
Dev_AryaDev_Arya
Thanks guys, parenthesis were wrong :P , how stupid of me. But I cannot mark both the solutions as Best answer. Sorry for that.