Convert a SQL query to Fetchxml query RRS feed

  • Question

  • I have scenario where 'Client' Entity linked to 'Location Address' Entity. There is a 'Is this current location address' option set field in Location Address entity. For 'Client' Entity Records the 'Is this current address' field is marked Yes for multiple location addresses. So i have to fire a query that calculates the persons records having multiple location addresses marked as yes.

    Following is the SQL query for the same.           

    select mcg_name, count(mcg_isthiscurrentrecord) 
    from mcg_client, mcg_locationaddress 	    
    where mcg_client.mcg_clientid = mcg_locationaddress.mcg_clientid and mcg_isthiscurrentrecord=1 	    
    group by mcg_name             
    having count(mcg_isthiscurrentrecord) > 1

    I want an Fetchxml query for the same. Please help me out

    Thanks in advance !!!

    Friday, June 13, 2014 9:50 AM


  • You can't do this solely with a FetchXML query, as FetchXML does not have the equivalent of the SQL HAVING clause.

    You can retrieve the mcg_name, count(mcg_isthiscurrentrecord) values easily enough with FetchXML (see http://msdn.microsoft.com/en-us/library/gg309565.aspx ), but then you will have to filter out those count(mcg_isthiscurrentrecord) = 1 outside of the FetchXML

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, June 16, 2014 12:39 PM