I’m pleased to release the latest update for SQL 4 CDS today! For this update I’ve focused on common problems I’ve seen people encounter with their queries from my telemetry. These updates should either mean that more queries can be executed immediately, or the error message is more descriptive to help solve common errors.
SQL handling improvements
This release fixes a bug which caused an error when referring to an aliased column in an
ORDER BY clause, so you can now correctly run:
SELECT name, firstname, count(*) as count FROM account INNER JOIN contact ON parentcustomerid = account.accountid GROUP BY name, firstname ORDER BY count
For queries that need to be filtered in memory by SQL 4 CDS as the
WHERE clause could not be converted to FetchXML, this release also fixes bugs:
- comparing two lookup values
- queries that include additional parentheses
- filter date/time values
SELECT name FROM account WHERE telephone1 = '+44' + telephone2 AND (createdby = ownerid) AND createdon >= '2020-01-01'
When limiting the number of rows to return using the
TOP clause, I’d often see errors when people enclose the number in brackets. This is now fixed so you can correctly run:
SELECT TOP (10) name FROM account
To get the number of records, I’d normally write
COUNT(*) but I’d often see people using
COUNT(1) instead. This would work, but would trigger SQL 4 CDS to calculate the count in memory instead of converting it to an aggregate FetchXML query. Now
COUNT(1) and even
SUM(1) are all treated identically and will take advantage of more efficient execution whenever possible.
Date filters often include calculations involving
GETDATE() to get the current date & time, so I’ve now added support for this and other similar functions. You can now filter records using queries such as:
SELECT name FROM account WHERE createdon < DATEADD(month, -1, GETDATE())
This can’t be converted directly to FetchXML (see my earlier post for more information on the dynamic date filtering options offered by FetchXML), so where possible SQL 4 CDS will calculate the filter value and pass this into the FetchXML as:
<fetch> <entity name="account"> <attribute name="name" /> <filter> <condition attribute="createdon" operator="lt" value="2020-07-28 21:46:23" /> </filter> </entity> </fetch>
Although most queries people run are
SELECT, I personally use it almost as much for
DELETE as well. To make it simpler for some bulk update scenarios I’ve added support for the
REPLACE function, so you can write:
UPDATE new_blogpost SET new_content = REPLACE(new_content, 'Dataflex Pro', 'CDS')
T-SQL endpoint improvements
I’ve been doing quite a bit of work recently investigating the preview TDS endpoint for executing SQL queries directly against CDS, and one common issue I keep hitting is a valid query like this will generate errors:
SELECT account.name, contact.fullname FROM account INNER JOIN contact ON account.accountid = contact.parentcustomerid
If you use SQL 4 CDS to execute the query against the T-SQL endpoint (you can turn this option on in the Settings dialog), the query will be automatically rewritten behind the scenes as:
SELECT account.name, contact.fullname FROM account AS account INNER JOIN contact AS contact ON account.accountid = contact.parentcustomerid
This release also improves the user experience when enabling or disabling the TDS endpoint from within the SQL 4 CDS Object Explorer pane.
Error handling improvements
When you update a polymorphic lookup field, e.g. the
parentcustomerid field on a contact, or
regardingobjectid on an activity, you need to specify the entity type of the related record as well as the ID. This is a common error I see when someone writes a query such as:
UPDATE contact SET parentcustomerid = 'FAA30FF7-EDB2-4053-9A3C-BDBCEF645FAA' WHERE contactid = 'AA4E2264-9375-4CFF-B160-1CEF86BB94C7'
This needs to be written instead as:
UPDATE contact SET parentcustomerid = CREATELOOKUP('account', 'FAA30FF7-EDB2-4053-9A3C-BDBCEF645FAA') WHERE contactid = 'AA4E2264-9375-4CFF-B160-1CEF86BB94C7'
and the error message will now direct you to this solution.
I also commonly see errors due to people using double quotes to enclose string literals instead of single quotes. By default, SQL treats double quotes to enclose identifiers such as table or column names. There is an option in the Settings dialog to change this (untick the “Quoted Identifiers” checkbox), but if you use this incorrectly the error message will now suggest that you use single quotes instead.
FetchXML to SQL Conversion
Inspired by Michael Ochs’ virtual entity provider for connecting to SQL databases, I’ve now updated the conversion from FetchXML to SQL and made it available as a separate package for you to reuse in your own tools!
You could already do this using the full SQL 4 CDS engine, but this stripped down version makes it easier to include this functionality in plugins.