SQL转换成FetchXML的工具网站http://www.sql2fetchxml.com 使用说明

Charts and Reports in CRM are very powerful, a lot of people using Advanced Find to generate the FetchXML to help them get some ideas about writing FetchXML, but Advanced Find has some limitations, such as cannot aggregate, no table hint, no unique filter, cannot order, top number, alias, group by and etc.

 

CRM里面的图表、报表都非常强大，很多人使用高级查找来生成FetchXML帮助他们编写相关查询语句。但是微软CRM中的高级查找有一些局限，并不能生成FetchXML支持的所有类型的查询语句，如表的nolock, 排序，top，别名，group by， 分页等等。

 

Today, a new tool go online for those developers who knows SQL, but not familiar with FetchXML! Please go to www.sql2fetchxml.com to have a try.

今天，网上出现了一个新的工具，可以帮助那些懂SQL语句，但不熟悉FetchXML的开发人员编写FetchXML的查询语句！请访问 http://www.sql2fetchxml.com  

Any feedback will be appreciated.

 

Here is the FAQ:常见问题：

• Q1: What's the table name for the query? 表名用什么？
• A1: table name will be the entity name, such as "account", "contact", "task" etc. 用实体的逻辑名
• Q2: What functionalities are supported by this tool? 这个工具支持哪些功能
• A2: Features supported by FetchXML will be supported by this tool, but since FetchXML only support part of SQL features, so, please be aware of this. 这个工具支持所有fetchxml支持的查询功能
  • We can support: top, distinct, nolock, where, order by, inner join, left outer join, aggregate (count, max, min, avg, sum), group by, alias 
  • CRM functions ( using eq and x prefix, for example:  where createdon = xlastyear() or modifiedon=xyesterday(), if any parameters, you can put them in the function, like modifiedon = lastxweeks(4) or modifiedon = inorafterfiscalperiodandyear(4,2006)
  • For link entity conditions, they should be put along with the nearest join conditions, for example, select a.name, c.firstname, c.lastname from account a inner join contact c on a.accountid=c.parentcustomerid and (contact related conditions) where (account related conditions) 当需要写连接实体的筛选条件时，我们需要把连接实体的条件写在on 的条件里面，如示例。

 

Here are some samples:

 如下是一些示例

• Simple table query:

Select name from account where name like '%Kingswaysoft%'

 

• Query with top, nolock, order by

Select top 10 name from account with (nolock) where creditlimit > 1000000 order by creditlimit

 

• Query with Join and with alias

select a.name, c.firstname, c.lastname from account a inner join contact c on a.accountid=c.parentcustomerid and (c.telephone1 is not null or c.telephone2 is not null or c.telephone3 is not null) where a.creditlimit > 10000

 

• Aggregate

Select count(*) from account

Select count(distinct name) from account

Select avg(creditlimit) from account

 

• Group by

Select count(accountid) from account group by accountratingcode

 

• Paging (using SQL 2012 syntax)

select * from task order by subject offset 10 rows fetch next 10 rows only

 

转载：http://social.microsoft.com/Forums/zh-CN/crmchinese/thread/6e9d57b3-e254-49e2-88a8-ef8a40f774f8