Announcements
Hello All,
This is humble request, if possible, then please reply early for below concern.
Please help me to convert fetch xml into sql query
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="logmechanism">
<attribute name="name" />
<attribute name="createdon" />
<attribute name="src_bo" />
<attribute name="toendpointid" />
<attribute name="messagestatus" />
<attribute name="messageid" />
<attribute name="errordetails" />
<attribute name="src_app" />
<attribute name="messagetype" />
<attribute name="logmechanismid" />
<attribute name="workorder" />
<order attribute="createdon" descending="true" />
<filter type="and">
<condition attribute="messagetype" operator="eq" value="0" />
<filter type="or">
<condition attribute="src_bo" operator="eq" value="service" />
<condition attribute="src_bo" operator="eq" value="feedback_new" />
</filter>
<condition attribute="createdon" operator="on-or-before" value="2023-05-24" />
</filter>
<link-entity name="workorder" from="workorderid" to="workorder" link-type="inner" alias="al">
<filter type="and">
<condition attribute="systemstatus" operator="not-in">
<value>2</value>
<value>1</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>
Your query will look like :
Select L.name, L.createdon, L.src_bo, L.toendpointid, L.messagestatus, L.messageid, L.errordetails, L. src_app, L.messagetype, L.logmechanismid, L.workorder, W.systemstatus from logmechanism L
inner join workorder W on L.workorderid = W.workorder
Where L.messagettype = 0
AND (L.src_bo = "service" OR L.src_bo = 'feedback_new')
AND L.createdon <= '2023-05-24' and W.systemstatus NOT IN (2,1)
ORDER BY L.createdon
Mark my answer As Verified if it is helpful.
Hey!
I think it would be something like this:
SELECT L.name, L.createdon, L.src_bo, L.toendpointid, L.messagestatus, L.messageid, L.errordetails, L.src_app, L.messagetype, L.logmechanismid, L.workorder FROM LogMechanism L INNER JOIN WorkOrder AL ON AL.workorderid = L.workorder AND (AL.systemstatus NOT IN (1,2)) WHERE messagettype = 0 AND (src_bo = 'service' OR src_bo = 'feedback_new') AND createdon <= '2023-05-24' ORDER BY createdon
Regards,
Use Fetch XML Builder in XRM Toolbox.
André Arnaud de Cal... 291,359 Super User 2024 Season 2
Martin Dráb 230,370 Most Valuable Professional
nmaenpaa 101,156