Dynamics Community Forum Thread Details

SQL to FetxhXML Conversion for linked-entity attribute is null

Hi,

I have the following SQL which works fine:

SELECT DISTINCT
	fullname, 
	cdi_postedsubscription.cdi_postedsubscriptionid
FROM contact
LEFT OUTER JOIN cdi_postedsubscription cdi_postedsubscription 
	ON cdi_postedsubscription.cdi_contactid = contact.contactid
WHERE contact.statecode = 0
AND cdi_postedsubscriptionid is NULL
ORDER BY fullname

when I try to convert this to FetchXML where will I add the following line?

AND cdi_postedsubscriptionid is NULL

If I add it in the on statement of the link-entity as follows then it returns an incorrect result:

If I add it in the on statement of the link-entity as follows then it returns an incorrect result:

<fetch mapping="logical" distinct="true" version="1.0">
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="cdi_postedsubscription" from="cdi_contactid" to="contactid" alias="cdi_postedsubscription" link-type="outer">
      <attribute name="cdi_postedsubscriptionid" />
      <filter>
        <condition attribute="cdi_postedsubscriptionid" operator="null" />
      </filter>
    </link-entity>
  </entity>
</fetch>

If I add it to the filter of the contact as follows then it displays an error saying cdi_postedsubscriptionid does not exist in contact entity:

<fetch mapping="logical" distinct="true" version="1.0">
  <entity name="contact">
    <attribute name="fullname" />
    <order attribute="fullname" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="cdi_postedsubscriptionid" operator="null" />
    </filter>
    <link-entity name="cdi_postedsubscription" from="cdi_contactid" to="contactid" alias="cdi_postedsubscription" link-type="outer">
      <attribute name="cdi_postedsubscriptionid" />
    </link-entity>
  </entity>
</fetch>

Thanks for any pointers

Richard

Quick Links

Leaderboard