Convert SQL to FetchXML error?

Question

Just a quick one trying to convert the below to Fetch XML, however have the error below.

Error: Error occurred when parsing the SQL script: Unsupported statement type: DECLARE

DECLARE 
    @OrganisationId uniqueidentifier

SELECT
    cil.mm_topprioritystatus,
    cil.mm_achievedcatalogproductName,
    cil.mm_achievedcatalogproductstatus,
    ci.mm_key
FROM
    mm_catalogitemtorganisationlinker cil
INNER JOIN
    mm_catalogitem ci on 
        ci.mm_catalogitemId = cil.mm_catalogitem    
where 
    mm_organisation = @OrganisationId
    and ci.mm_key in (
        'LEVEL1',
        'LEVEL2',
        'LEVEL3',
        'LEVEL4'
    )

Please advise for the fetchXML version of above.

Answer 1

This is one possible solution:

<fetch>
  <entity name='mm_catalogitemtorganisationlinker' >
    <attribute name='mm_topprioritystatus' />
    <attribute name='mm_achievedcatalogproductName' />
    <attribute name='mm_achievedcatalogproductstatus' />
    <filter>
      <condition attribute='mm_organisation' operator='eq' value='{organizationid}' />
    </filter>
    <link-entity name='mm_catalogitem' from='mm_catalogitemid' to='mm_catalogitem' link-type='inner' alias='ci' >
      <attribute name='mm_key' />
      <filter>
        <condition attribute='mm_key' operator='in' >
          <value>LEVEL1</value>
          <value>LEVEL2</value>
          <value>LEVEL3</value>
          <value>LEVEL4</value>
        </condition>
      </filter>
    </link-entity>
  </entity>
</fetch>

Of course you have to replace the {organizationid} placeholder by code.