After launching our SQL2FetchXML website, we received a few inquiries about some special SQL scripts that cannot be converted to FetchXML queries using the free online utility. I here put together a list of the limitations with CRM FetchXML that I have observed. I will try to be thinking of the limitations by comparing it to what can be done through SQL script.
- RIGHT OUTER JOIN is not supported.
- You can't compare two fields directly. For instance, you won't be able to find an equivalent query for the following SQL script:
SELECT * FROM account WHERE telephone1 <> telephone2The right side of the comparison has to be a constant value.
You can't have OR condition across entities.(This is no longer an issue since CRM 2013 release)
- You can't use SQL functions in FetchXML query. CRM has support for some built-in functions, but any additional SQL functions are not supported.
- No support of UNIONs (Thanks to Dirk Fabricius for his contribution by commenting below).
- There is no support of a CASE / WHEN structure.
- When you issue a FetchXML query, the maximum number of records you get back from CRM server is 5,000 each time. If you want to get more records from CRM server, you would have to use paging cookie.
- You can't have more than 10 linked entities in a FetchXML query. It is possible to overcome this limit by creating or updating a QueryLinkEntityLimit setting, however this is generally not recommended. If you ever run into this situation, you would definitely want to re-visit your CRM data model or re-engineer your query.
- When you perform an aggregation, the maximum number that will participate in the aggregation will be 50,000 records. For instance, if you do a COUNT aggregation, the maximum value you can get back from CRM is 50,000 even though that you might have more records in the system. This is a by-design behavior which is for performance reason. This can be overcome by updating "AggregateQueryRecordLimit" setting, however it is generally not recommended.
- There is no way to use subquery.
- There is a wacky limitation that you can't have more than 2097 conditions per filter, according to Daniel Halan.
This is what I have got so far. Please let me know if I have missed anything.