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…

COUNT(DISTINCT) Support

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" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

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)
ORDER BY 1, 2

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" />
  </entity>
</fetch>

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)
ORDER BY 1, 2

Sorting on link entities

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

SELECT   c.firstname,
         a.name
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,
         a.name
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

UPDATE & DELETE Batch Sizes

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

2 Comments

  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.
    i.e.
    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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.