SQL 4 CDS 1.0.6 Released!

I’m very pleased to announce the release of SQL 4 CDS version 1.0.6! Please update your existing versions in XrmToolBox to get some great new features…


You can now use the count(DISTINCT col) aggregate function, e.g.:

SELECT count(DISTINCT firstname) AS distinct_names,
       count(firstname) AS names,
       count(*) AS contacts
FROM   contact
WHERE  statecode = 0

This will get the number of different first names, the number of active contacts with a first name and the total number of active contacts, and gets translated to the following FetchXML:

<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
  <entity name="contact">
    <attribute name="firstname" alias="distinct_names" aggregate="countcolumn" distinct="true" />
    <attribute name="firstname" alias="names" aggregate="countcolumn" />
    <attribute name="contactid" alias="contacts" aggregate="count" />
      <condition attribute="statecode" operator="eq" value="0" />

DATEPART support

You can also now group data using the DATEPART function, e.g. find out how many new leads you’ve received each month using:

SELECT   datepart(year, createdon) AS year,
         datepart(month, createdon) AS month,
         count(*) AS leads
FROM     lead
GROUP BY datepart(year, createdon), datepart(month, createdon)

This returns the year number, month number and the number of leads created in that month. The results are returned in date order. This translates to the following FetchXML:

<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
  <entity name="lead">
    <attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="leadid" alias="leads" aggregate="count" />
    <order alias="year" />
    <order alias="month" />

Because the dategrouping attribute in FetchXML supports a few more options than the standard T-SQL DATEPART function, I’ve extended it in SQL 4 CDS to allow some additional values as the first parameter. You can also use fiscalperiod and fiscalyear to group your data by your fiscal periods instead of calendar periods, e.g.:

SELECT   datepart(fiscalyear, createdon) AS year,
         datepart(fiscalmonth, createdon) AS month,
         count(*) AS leads
FROM     lead
GROUP BY datepart(fiscalyear, createdon), datepart(fiscalmonth, createdon)

Sorting on link entities

You can now sort your query based on a field from a joined table, e.g.:

SELECT   c.firstname,
FROM     contact AS c
         INNER JOIN
         account AS a
         ON c.parentcustomerid = a.accountid
ORDER BY c.firstname, a.name

Remember though that you can’t order by the joined table first and then the first table, so you can’t do:

SELECT   c.firstname,
FROM     contact AS c
         INNER JOIN
         account AS a
         ON c.parentcustomerid = a.accountid
ORDER BY a.name, c.firstname

If you use an order that isn’t supported by FetchXML you’ll get the error Order already applied to later link-entity


You know you can write more than just SELECT statements in SQL 4 CDS? You can now control the number of records that are processed for UPDATE and DELETE in each batch, which can help avoid timeouts if each one takes a long time because of plugins etc. To control the batch size, click on the Settings button in the toolbar:

Control UPDATE and DELETE batch size

Bug Fixes

As well as these new features there are various bug fixes included:

  • Fixed “Unhandled INSERT optimizer hints” error when running INSERT queries
  • Fixed UPDATE query to set values to NULL
  • Fixed IN & NOT IN criteria
  • Fixed converting FetchXML to SQL (e.g. in FetchXML Builder) when the query contains a <link-entity> with no content and aliases that aren’t valid SQL identifiers

Join the conversation


  1. Hi Mark,
    First of all, thank you for providing such a valuable solution with SQL-4-CDS.
    I was wondering if it is possible to perform an UPDATE query where I am setting a field value to some other field’s value. The example below shows what I am after and both fields would be of the same type. I have also tried using separate select and update statements with declared variables, as well as self joins. No luck so far.
    UPDATE new_someentity
    SET new_fieldone = new_fieldtwo
    WHERE new_name = ‘somevalue’

Leave a comment

Your e-mail address will not be published. Required fields are marked *