locked
SQL query to Fetchxml RRS feed

  • Question

  • Hello I want to convert following SQL query in FetchXML

    select at.ava_name ,at.createdon
    from ava_agreementtype at join ava_agreementtyperelation atr on at.ava_agreementtypeId=atr.ava_ChildId
    join ava_agreement ag on atr.ava_ParentId=ag.ava_agreementtypeId
    where ag.ava_AgreementId='0F8CA0AE-6D7F-E211-B35E-001DD8B825E8'

    I tried following :


      var fetchXml = "<fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>" +
                     " <entity name='ava_agreementtype'>" +
                      "<attribute name='ava_agreementtypeid'/> <attribute name='ava_name'/> <attribute name='createdon'/>" +
                      " <order descending='false' attribute='ava_name'/>" +
                       "<link-entity name='ava_agreementtyperelation' from='ava_childid' to='ava_agreementtypeid' visible='false' link-type='outer' alias='ava_agreementtyperelation'>" +
                       " </link-entity>" +
                            "<link-entity name='ava_agreement' from='ava_agreementtypeid' to='ava_parentid' visible='false' link-type='outer'>" +
                                 "<filter type='and'> <condition attribute='ava_agreementid' operator='eq' value='" + parentagreementid + "'/>" +                       
                             " </link-entity>" +                 
                       " </filter>" +
                       " </entity>" +
                       " </fetch>";

    Its not working :(. Can someone pls help me regarding this.

    Thanks in advance.

    Wednesday, February 27, 2013 10:16 AM

All replies

  • Please make is query in Advanced Find and click on Downlaod Fetchxml button.


    Regards Faisal

    Wednesday, February 27, 2013 11:15 AM
  • Swap

                             " </link-entity>" +                 
                       " </filter>" +

    With

                           " </filter>" +          
               " </link-entity>" +                 

    Or build your query in an advanced find as suggested to get the query you want and replace the necessary parts. 


    -


    Wednesday, February 27, 2013 11:24 AM
  • use advance find to query the data...

    ms crm

    Wednesday, February 27, 2013 1:21 PM
  • I guess i can't convert above query in single fetchxml. Reason being MS CRM 2011 query builder having capability of returning single root entity type data. So in join i should always have joins only with enity which I mentioned in from clause. As per my this understanding I removed second join and retrived that data in seperate query. 
    Friday, March 15, 2013 11:01 AM