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
Thanks
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,
o.statuscode,
ct.new_totalhours
<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" />
</link-entity>
</entity>
</fetch>
Pradnya07