0
list<string> lst=new list<string>();
lst.add('name');
system.debug('lst[]'+lst[0]);
 system.debug([select count() from account where lst[0]!='' limit 1]);

I am getting below error

Line: 4, Column: 51 unexpected token: '['

How to use List Index in soql.Please suggest

2
  • 1
    It seems like lst[0]!='' is a tautology, and that lst[0] is not a field. If you are trying to compare to the Account.Name field, try using dynamic soql to build your query. If not, does list<string> lst=new list<string>(); lst.add('name'); system.debug('lst[]'+lst[0]); system.debug([select count() from account where Name!=:lst[0] limit 1]); Feb 16 '17 at 16:08
  • If any of the below answers addressed your problem, please consider accepting it by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either.
    – Adrian Larson
    Feb 18 '17 at 21:50
2

You have to use dynamic SOQL if you want to use a variable as field selector in your query:

AggregateResult aggResult = Database.query('select count(Id) countResult from account where ' + lst[0] + ' != null limit 1');
System.debug(aggResult.get('countResult'));

Or even simpler:

Integer recordCount = Database.countQuery(Database.query('select count() from account where ' + lst[0] + ' != null');
5
  • Count() does not exist in SOQL as far as I know !!!
    – benahm
    Feb 16 '17 at 16:10
  • I updated my answer. This should work. Feb 16 '17 at 16:13
  • 1
    @benahm You're completely wrong about that.
    – Adrian Larson
    Feb 16 '17 at 18:22
  • @AdrianLarson nice solution ! Feb 16 '17 at 20:06
  • @AdrianLarson Yeah think that Count(*) does not exist, am I right ? :)
    – benahm
    Feb 17 '17 at 8:40
1

You can't use SOQL in this fashion, you'll need to write a dynamic SOQL query such as below. Additionally, you'll need to use Database.countQuery

list<string> lst=new list<string>(); lst.add('name');
system.debug('lst[]'+lst[0]);

String queryStr = '';

queryStr += 'select count() from account where '; queryStr += lst[0] + ' != \'\' limit 1';
system.debug(database.countQuery(queryStr));
5
  • Thanks for response.But my aim is to get count of all field values populated in records.Like in my list {Name,LastName,No_Of_Users__C} fields then it needs to be display count for all three fields
    – dany
    Feb 16 '17 at 16:29
  • list<string> lst=new list<string>(); lst.add('name'); lst.add('Total_Contacts__c'); for(integer i=0;i<=lst.size();i++){ String queryStr = ''; queryStr += 'select count() from account where '; queryStr += lst[i] + ' != \'\' '; system.debug(database.countQuery(queryStr)); }
    – dany
    Feb 16 '17 at 16:31
  • I have prepared above code .
    – dany
    Feb 16 '17 at 16:32
  • But getting below error{Line: 8, Column: 1 System.QueryException: value of filter criterion for field 'Total_Contacts__c' must be of type double and should not be enclosed in quotes}
    – dany
    Feb 16 '17 at 16:33
  • 1
    Yeah stick with != null. Safe across all field types.
    – Adrian Larson
    Feb 16 '17 at 18:49

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.