[SalesForce] Dynamic Query – System.QueryException: expecting a colon, found ‘.’

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.

Best Answer

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';
Related Topic