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

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

23 thoughts on “SQL 4 CDS 1.0.6 Released!”

  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’

  2. Thanks a lot Mark for this wonderful tool for sql 4 cds. Do you know when the support for declared variable will be released?

    1. I’d guess there’ll be more in that script that SQL 4 CDS doesn’t support beyond just variables. If you want to post the script as an issue on the GitHub site I can take a look, but you’ll probably be better off enabling the TDS endpoint on your instance and executing it through that

  3. Thanks , now I followed the instructions in the article, but I stil get the same messager “Unsupported statement: declare @ids table(idx int ….. “.
    I would have send a screenshot of the whole thing but I do not know how on this reply. When I have enabled T-SQL endpoint, I got “T-SQL Endpoint (Unavailable – OAuth authentication required)”. So where can I give the authentication?

      1. I have now logged in with OAuth and still get the same message. I have now “T-SQL Endpoint” it is enabled and green. The Any other tips?

  4. Mark,

    MAX works on some field but not others, when i try select max(createdon) from audit i get Aggregates are not supported
    See the Execution Plan tab for details of where this error occurred, but when i select max(operation) from audit the query runs without error.

    Is there something i can do to get the MAX function to work on createdon?

    Thank you,
    Jim

  5. Would like to know if is possible to declare variables as in the next example to execute queries using this variable to update info as in the next example
    DECLARE @FY22 int
    select @FY22=new_fiscalyearid from new_fiscalyear where new_name = ‘FY22’
    print @FY22

    Thank you so much for this nice tool!!

Leave a Reply

Your email address will not be published. Required fields are marked *

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