Expressions - MarkMpn/Sql4Cds GitHub Wiki
SQL 4 CDS supports a wide range of expressions. It aims to support all the same expression types as shown in the T-SQL Expressions documentation.
However, only a subset of the standard T-SQL functions are currently implemented in SQL 4 CDS. The supported functions are listed below. If other functions would be useful, please create an issue with the details of the required function.
Aggregate Functions
[!NOTE] When using the
OVERclause, theRANGEandUNBOUNDED FOLLOWINGoptions are not supported. See the Window Functions page for more details
Collation Functions
Configuration Functions
Conversion Functions
Data Type Functions
Date & Time Functions
- CURRENT_TIMESTAMP
- DATEADD
- DATEDIFF
- DATEPART
- DATETRUNC
- DAY
- GETDATE
- GETUTCDATE
- MONTH
- SYSDATETIME
- SYSDATETIMEOFFSET
- SYSUTCDATETIME
- YEAR
JSON Functions
Logical Functions
Metadata Functions
Ranking Functions
[!NOTE] The
RANGEandUNBOUNDED FOLLOWINGoptions are not supported. See the Window Functions page for more details
String Functions
- ASCII
- CHAR
- CHARINDEX
- LEFT
- LEN
- LOWER
- LTRIM
- NCHAR
- PATINDEX
- REPLACE
- RIGHT
- RTRIM
- STUFF
- SUBSTRING
- TRIM
- UNICODE
- UPPER
Security Functions
For the USER_NAME function, only the variant with no parameter value is supported. This returns the name of the current user.
System Functions
- @@ERROR
- @@IDENTITY
- @@ROWCOUNT
- ERROR_LINE
- ERROR_MESSAGE
- ERROR_NUMBER
- ERROR_PROCEDURE
- ERROR_SEVERITY
- ERROR_STATE
- FORMATMESSAGE
- ISNULL
- NEWID
The @@IDENTITY function returns an EntityReference type value instead of the normal int type.
Predicates
For the CONTAINS function, only simple CONTAINS(column, 'word1 OR word2 OR word3') usages are allowed. Multiple columns or
more complex search conditions are not supported. This pattern can be used on multi-select picklist ("Choices") fields to find
records that have one of a set of values selected, and will be translated to the FetchXML containvalues operator where possible.