how to convert the cast statement and case statement from SQL query to fetch xml RRS feed

  • Question

  • Hi All;

    Below is my query and which i need to fetch xml in crm
    Does any one help me to convert the cast statement and case statement  from SQL query to fetch xml

    Any help on this is much appreciated

    SELECT     o.new_mainprogramme_id AS 'MainProgramme',o.opportunityid,o.new_entrypoint,
    o.statuscode AS 'OppStatus' ,o.parentcontactid,o.new_subprogramme_id AS 'SubProgramme' ,o.ownerid  AS 'BusinessAdvisor',
    o.statuscode AS 'ApplicantStatus' ,
     ct.new_totalhours AS 'TotalHours',
    CAST(ct.new_totalhours / 60 AS VARCHAR(10)) + ':' + RIGHT('0' + CAST(ct.new_totalhours % 60 AS VARCHAR(2)), 2) AS 'TotalHours1',
     (CASE WHEN ct.new_totalhours BETWEEN 0 AND 240 THEN '0 To 4 hrs ' WHEN ct.new_totalhours BETWEEN 241 AND 
                          480 THEN '5 To 8 hrs' WHEN ct.new_totalhours BETWEEN 481 AND 720 THEN '9 To 12 hrs' WHEN ct.new_totalhours BETWEEN 721 AND 
                          960 THEN '13 To 16 hrs' WHEN ct.new_totalhours BETWEEN 961 AND 1200 THEN '17 To 20 hrs' WHEN ct.new_totalhours > 1201 THEN 'More than 20 hrs' END) 
                          AS GroupNoOfHours
    FROM         opportunity o
     LEFT OUTER JOIN contact AS ct ON ct.contactid = o.parentcontactid 
    group by 
    o.new_mainprogramme_id ,o.opportunityid,o.new_entrypoint,
    o.statuscode ,o.parentcontactid,o.new_subprogramme_id ,o.ownerid,

    <fetch mapping="logical">
      <entity name="opportunity">
        <attribute name="new_mainprogramme_id" alias="MainProgramme" groupby="true" />
        <attribute name="opportunityid" groupby="true" />
        <attribute name="new_entrypoint" groupby="true" />
        <attribute name="statuscode" alias="OppStatus" groupby="true" />
        <attribute name="parentcontactid" groupby="true" />
        <attribute name="new_subprogramme_id" alias="SubProgramme" groupby="true" />
        <attribute name="ownerid" alias="BusinessAdvisor" groupby="true" />
        <attribute name="statuscode" alias="ApplicantStatus" />
        <link-entity name="contact" to="parentcontactid" from="contactid" alias="ct" link-type="outer">
          <attribute name="new_totalhours" alias="TotalHours1" groupby="true" />


    Wednesday, April 9, 2014 9:10 AM

All replies

  • Data manipulation available in SQL such as Case, Convert cannot be performed in FetchXML.

    In case your requirement is for FetchXML based CRM reports, then the one workaround I can think of is to leverage the SSRS report designer Expression feature. That can be a alternate to overcome FetchXML limitation.

    Hope it would help you. Let me know in case of any help needed.



    Monday, April 14, 2014 10:56 PM
  • Yes, There's no equivalent function in FetchXml. Instead, you will need to return relevant field value, and process the CASE logic in the calling code. If this FetchXml is to be used in a report, then you may be able to implement the CASE logic in a calculated field on the report

    My Blog
    Follow Me on Twitter

    Tuesday, April 15, 2014 7:25 PM