3

Suppose I have one code, HI, which is mapped to these values: HIRT, HIFS,HISL,HIST,HIGC,HIAN,HIIS,HICV,HISR,HOLI. When I select HI those values are are passed into a dynamic soql query. For a small list of values, the dynamic query is working. However, a longer list of values causes an error.

SELECT
    Id, Name, Hotel_Location_Number__c, Hotel_Inn_Code__c, Future_Brand__c,
    Previous_Brand__c, Hotel_Name__c, Hotel_City__c, Hotel_State__c, Hotel_Location__c,
    Hotel_Country__c, Project_Type__c, Project_Status_Code__c, Lic_Term__c,
    Hotel_Status__c, Brand_Group_Code__c
FROM Project__c
WHERE Name != null
AND Future_Brand__c IN('HIRT', 'HIFS', 'HISL', 'HIST', 'HIGC', 'HIAN', 'HIIS', 'HICV', 'HISR', 'HOLI', ...)
ORDER BY Project_ID__c DESC

Error:

System.QueryException: expecting a colon, found '.'

My code is

List<String> futureBrandGroups=new List<String>(); 
            Map<String,BrandMapping__c> brandCode=BrandMapping__c.getAll();
            List<BrandMapping__c> brandValues=brandCode.Values();
            List<BrandMapping__c> brandMapping=[select name from BrandMapping__c where Brand_Group_Code__c=:futureBrandGroup];
             for(BrandMapping__c brandMappings:brandMapping)
             {  

                 String brands= '\''+ String.escapeSingleQuotes(brandMappings.name) + '\'';
                 futureBrandGroups.add(brands);
             }    
            strTempSoql += 'and Future_Brand__c IN'+futureBrandGroups;

futureBrandGroup='HI' name=HIRT, HIFS,HISL,HIST,HIGC,HIAN,HIIS,HICV,HISR,HOLI.

For small values there is no problem in the soql query in my apex class. However, these longer lists of values causes an error.

11

Explanation

The core problem here is that implicit type conversion from List<String> to String will cause truncation. It looks like this happens after the tenth element. You can see it for yourself by running the following script in Execute Anonymous:

List<Integer> numbers = new List<Integer>();
for (Integer i = 0; i < 25; i++)
    numbers.add(i);
system.debug('' + numbers);

The above script will yield this debug:

DEBUG|(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, ...)

Solution

You need to merge in a joined string:

strTempSoql += 'AND Future_Brand__c IN (' + String.join(futureBrandGroups, ',') + ')';

You want it to merge in something like:

Future_Band__c IN ('Value 1', 'Value 2', 'Etc.')

But when using implicit type coercion, eventually the list becomes truncated. So instead it becomes:

Future_Band__c IN ('Value 9', 'Value 10', ...)

If you are doing the query in the same context, you could instead merge in a dynamic reference:

strTempSoql += 'AND Future_Brand__c IN :futureBrandGroups';
| improve this answer | |
  • strTempSoql += 'AND Future_Brand__c IN :futureBrandGroups'; this is not working in apex error is comming .Code is saved but when we are running the page error in controller class variable futureBrandGroups is not found... – lalit Nov 8 '16 at 6:13
  • Thanks it is working............... strTempSoql += 'AND Future_Brand__c IN (' + String.join(futureBrandGroups, ',') + ')'; – lalit Nov 8 '16 at 8:40
  • @lalit Yes, notice I said the binding approach will only work if you run the query in the same context. – Adrian Larson Nov 8 '16 at 13:01
  • @lalit If this answer 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 Nov 8 '16 at 13:01

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.