Dynamic Query - System.QueryException: expecting a colon, found '.'

Question

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.

Answer 1

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