SQL 4 CDS Additional Processing

SQL 4 CDS attempts to convert your SQL query into FetchXML so it can be executed natively by CDS. However, SQL is a very rich language and there are a lot of queries that can’t be directly converted. For example, trying to compare two fields:

SELECT *
FROM   contact
WHERE  firstname = lastname AND
       statecode = 0

There is no way to represent this in FetchXML, but it’s still a useful query to write. To support this, the SQL will be converted to FetchXML that includes all the data required to evaluate it:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="contact">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="contactid" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Note how the conversion has included as much of the filter in the FetchXML as possible. In this case the results will only include active contacts.

The rest of the query will be evaluated by SQL 4 CDS itself. It will get a list of all active contacts from CDS, but the Results tab will only show the contacts where the first and last names match.

Please bear this in mind if you take this FetchXML and use it in another tool such as FetchXML Builder – you won’t get the additional filtering or other query modifications that you would when you execute the query in SQL 4 CDS, simply because there is no way in FetchXML to represent these queries.