locked
Fetchxml filtering problem RRS feed

  • Question

  • Here is a SQL query:

    select  p15_guestid,g.p15_bookdate, p15_contactguestsid, g.modifiedon,p15_guestsid 
    from p15_guests g
    inner join p15_bookings b on g.p15_guestid = b.p15_bookingsid 
    left outer join p15_evaluation e on e.p15_bookingid = b.p15_bookingsid
    where  e.p15_bookingid is null
    order by g.p15_bookdate


    Which has the equivalent FetchXml:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="p15_guests"><attribute name="statuscode"/><attribute name="p15_guestid"/>
    <attribute name="p15_bookdate"/><attribute name="p15_contactguestsid"/><attribute name="modifiedon"/><attribute name="p15_guestsid"/>
    <order attribute="p15_bookdate" descending="true"/>
    <link-entity name="p15_bookings" from="p15_bookingsid" to="p15_guestid" alias="aa">
    <link-entity name="p15_evaluation" from="p15_bookingid" to="p15_bookingsid" alias="ab" link-type="outer">
    <filter type="and"><condition attribute="p15_bookingid" operator="null"/></filter></link-entity>
    </link-entity></entity></fetch>

    The problem is that the filter on the FetchXml doesn't seem to be working properly. 
    The SQL properly returns 226 rows while the FetchXml improperly returns 229 (it includes 3 rows that should be filtered out).


    Is there another equivalent FetchXml expression I could use?  Is this a known bug in FetchXml?  Or is it merely that my translation from SQL to FetchXml is flawed?

    Much thanks!
    Wednesday, November 25, 2009 12:46 AM

Answers

  • I don't think you'll be able to get what you want using one FetchXML query, due to the limitations of FetchXML. I expect that, if you use SQL Profiler to look at the SQL actually generated from your FetchXML, then you'll find it's not the same as your suggested SQL.

    The main workaround would be to return all guests with bookings from one FetchXML query, then use another query to find the exclusions, and remove them from the results
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Tuesday, December 1, 2009 9:56 AM
    Moderator

All replies

  • Hi

    Try to figure out whether the count difference is due to inactive records in CRM.

    If not try to build a Advanced find view based on your SQL Query, verify the count. If the the count matches with your SQL query, obtain the fetch XML from the advanced find it self. To obtain the fetch XML from advanced view refer http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

    Regards
    Vinoth
    Wednesday, November 25, 2009 3:40 AM
  • Thanks so much for your help Vinoth. 

    Initially this problem arose because we were trying to build and Advanced Find on all the "guests" joined to "bookings" that were NOT IN "evaluations".
    It couldn't be done through the Advanced Find UI.

    I wrote the underlying SQL that was to return the proper result set and then used one of the FetchXml builder to code the proper FetchXml.

    When I ran the FetchXml it did not return filter out the proper rows.

    I modified the FetchXml to not use "null" but instead use "not-in" and the query still doesn't filter out the rows I'm trying to exclude.

    Have you ever encountered this?

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="p15_guests"><attribute name="statuscode"/><attribute name="statecode"/><attribute name="p15_guestid"/>
    <attribute name="p15_bookdate"/><attribute name="p15_contactguestsid"/><attribute name="modifiedon"/><attribute name="p15_guestsid"/>
    <order attribute="statuscode" descending="false"/>
       
    <link-entity name="p15_bookings" from="p15_bookingsid" to="p15_guestid" alias="aa">
    <link-entity name="p15_evaluation" from="p15_bookingid" to="p15_bookingsid" alias="ab" link-type="outer">
    <filter type="and"><condition attribute="p15_bookingid" operator="not-in"><value>391B6EC8-60B1-DE11-9E42-005056A94A89</value><value>C7F537A5-6978-DE11-BA12-005056B928A3</value></condition></filter></link-entity>
    </link-entity></entity></fetch>
    Tuesday, December 1, 2009 1:22 AM
  • I don't think you'll be able to get what you want using one FetchXML query, due to the limitations of FetchXML. I expect that, if you use SQL Profiler to look at the SQL actually generated from your FetchXML, then you'll find it's not the same as your suggested SQL.

    The main workaround would be to return all guests with bookings from one FetchXML query, then use another query to find the exclusions, and remove them from the results
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Tuesday, December 1, 2009 9:56 AM
    Moderator