How can I convert this T-SQL to Fetch-XML?

Question

I want to use this SQL in a Dynamics CRM report. I can't work out how to convert it to Fetch-XML.

select p.ProductNumber "Plan Number",p.Name,p.price "Monthly Rate",count(*) "Group", '0' "Direct Debit"
from contact c,product p 
where c.integ_schemeid = p.ProductId
and c.ParentCustomerId is not null
group by p.ProductNumber,p.Name,p.price

union

select p.ProductNumber "Plan Number",p.Name,p.price "Monthly Rate", '0' "Group", count(*) "Direct Debit"
from contact c,product p 
where c.integ_schemeid = p.ProductId
and c.ParentCustomerId is null
group by p.ProductNumber,p.Name,p.price

http://www.sql2fetchxml.com fails on this occasion.

Answer 1

You can't convert that query to FetchXML, because unions aren't supported. What you'll need to do is look into merging the queries into 1 if possible. Even if it means duplicating columns and using conditional statements within your report to display the relevant data instead. For example, if you simplified the query to this:

select p.ProductNumber "Plan Number", p.Name, p.price, c.ParentCustomerId
from product p 
inner join contact c on c.integ_schemeid = p.ProductId

This can be converted to the following fetchxml:

<fetch mapping="logical">
  <entity name="product">
    <attribute name="ProductNumber" alias="Plan Number" />
    <attribute name="Name" />
    <attribute name="price" />
    <link-entity name="contact" to="ProductId" from="integ_schemeid" alias="c" link-type="inner">
      <attribute name="ParentCustomerId" />
    </link-entity>
  </entity>
</fetch>

Then, because you have all of the data in 1 dataset including contacts with null ParentCustomerIds, you can group within your report instead of within the query.