FetchXML for SQL Join having filter

Question

I am trying to build FetchXML equivalent of SQL query, I am quite new in using FetchXML:

SELECT o.opportunityid,c1.accountid
FROM dbo.opportunity o
LEFT JOIN dbo.account c1 on o.customerid = c1.accountid and o.customeridtype = 1

into

<fetch mapping="logical" version="1.0">
  <entity name="opportunity">
  <attribute name="opportunityid" />
    <link-entity name="account" from="accountid" to="customerid" alias="A1" link-type="outer" >
        <filter type="and" >
            <condition attribute="customeridtype" operator="eq" value="1" />
        </filter>
    <attribute name="accountid" /> 
    </link-entity>

but this is throwing error saying that attribute "customeridtype" doesn't exist in entity "account". that attribute is from opportunity entity as in SQL query. How can I fix this?

I can't take out the filter outside the link, as it still got few more joins and that filter should be applied only to that join. However, i have downloaded entities separately in to tables and made required in joins in SQL itself — Vijay Ande, Apr 22 '20 at 05:30

score 0 · Answer 1 · answered Feb 19 '20 at 13:13

I just fired this in one of my Dynamics instance and gave correct result

<fetch>
      <entity name="opportunity" >
        <attribute name="opportunityid" />
        <attribute name="customeridtype" />
        <filter type="and" >
          <condition attribute="customeridtype" operator="eq" value="1" />
        </filter>
        <link-entity name="account" from="accountid" to="customerid" link-type="outer" alias="Account" >
          <attribute name="accountid" alias="AccountId" />
        </link-entity>
      </entity>
    </fetch>

score 0 · Answer 2 · answered Feb 19 '20 at 13:17

Take out the filter from inside link-entity xml node to outside entity node.

You can try XrmToolBox fetchxml builder or Kingswaysoft sql2fetchxml online tool.

<fetch mapping="logical" version="1.0">
  <entity name="opportunity">
  <attribute name="opportunityid" />

    <filter type="and" >
        <condition attribute="customeridtype" operator="eq" value="1" />
    </filter>

    <link-entity name="account" from="accountid" to="customerid" alias="A1" link-type="outer" >
        <attribute name="accountid" />   
    </link-entity>

FetchXML for SQL Join having filter

2 Answers2