2

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.

9

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

Your code is producing the error because the SQL statement is invalid. There is no way this code will work even with short values. Here's why:

The line

strTempSoql += 'and Future_Brand__c IN'+futureBrandGroups;

...produces this result:

and Future_Brand__c INSystem.Collections.Generic.List`1[System.String]

...because you assumed the .ToString() function of the List object would return a comma-separated list prefixed with an open parenthesis and suffixed with a closing parenthesis.

You should change your line to this:

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

...which converts the list to an array, then joins the array into a comma-separated list, pre- and suff-ixed with the needed parenthesis.

I uppercased 'AND' and 'IN' to make it stand out, and, I added the space in front of the IN to make it more readable.

I don't know why it seems to work with short lists, I could not get it to work with any size list.

Note: I assumed your code is in C#, not Java. It may be the case in Java that a list's ToString() converts it to comma strings. If that's the case... mea culpa...

  • His code is in APEX. Neither c# nor Java. – Sebastian Kessel Nov 7 '16 at 21:30
  • 1
    Doh! !@#$#! Ok, I see this is in the SalesForce group, which might explain my misunderstanding. It was a related question, so I answered it. Well, I tried... Thanks for pointing that out @Sebastian! – Andrew Jennings Nov 7 '16 at 22:43

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.