For expressions used to define column derivation and transformations, PK Submit supports the rules set by Microsoft. (The source of the information in this section can be found on Microsoft’s website.)
Transformation expressions can be used to create calculated columns. A second use is to create an aggregate column. Similar to a calculated value, an aggregate performs an operation based on the complete set of rows in the table. A simple example is to count the number of rows returned in the set.
When you create an expression, refer to columns using their column names.
Note: If a column is used in an expression, then the expression is said to have a dependency on that column. If a dependent column is renamed or removed, no exception is thrown. An exception will be thrown when the now-broken expression column is accessed.
When you create an expression for a filter, enclose strings with single quotation marks:
LastName = 'Jones'
If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.
And, Between, Child, False, In, Is, Like, Not, Null, Or, Parent, True
If a column name satisfies one of the above conditions, it must be wrapped in either square brackets or the ` (grave accent) quotes. For example, to use a column named Column# in an expression, you would write either [Column#]
Total * [Column#]
or `Column#`
Total * `Column#`
If the column name is enclosed in square brackets, then any ] and \ characters (but not any other characters) in it must be escaped by prepending them with the backslash (\) character. If the column name is enclosed in grave accent characters then it must not contain any grave accent characters in it. For example, a column named Column[]\ would be written:
Total * [Column[\]\\]
or
Total * `Column[]\`
User-defined values may be used within expressions to be compared with column values. String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character). Date values should be enclosed within pound signs (#) or single quotes (') based on the data provider. Decimals and scientific notation are permissible for numeric values. For example:
FirstName = 'John'
Price <= 50.00
Birthdate < #1/31/82#
For columns that contain enumeration values, cast the value to an integer data type. For example:
EnumColumn = 5
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When you create comparison expressions, the following operators are allowed:
<, >, <=, >=, <>, =, IN, LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
To concatenate a string, use the + character.
Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:
ItemName LIKE '*product*'
ItemName LIKE '*product'
ItemName LIKE 'product*'
Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
Var (Statistical variance).
For example, to create a summary of figures in a column named Price:
Sum(Price)
Note: If you use a single table to create an aggregate, all rows would display the same value in the column.
If a table has no rows, the aggregate functions will return null.
An aggregate can only be applied to a single column and no other expressions can be used inside the aggregate.
The following functions are also supported:
CONVERT: Converts particular expression to a specified .NET Framework Type.
Convert(expression, type)
where:
expression – The expression to convert.
type – .NET type to which the value will be converted.
Example:
myDataColumn.Expression="Convert(total, 'System.Int32')"
All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.
LEN: Gets the length of a string.
LEN(expression)
where: expression – The string to be evaluated.
Example:
myDataColumn.Expression="Len(ItemName)"
ISNULL: Checks an expression and either returns the checked expression or a replacement value.
ISNULL(expression, replacementvalue)
where:
expression – The expression to check.
replacementvalue – If expression is null, replacementvalue is returned.
Example:
myDataColumn.Expression="IsNull(price, -1)"
IIF: Gets one of two values depending on the result of a logical expression.
IIF(expr, truepart, falsepart)
where:
expr – The expression to evaluate.
truepart – The value to return if the expression is true.
falsepart – The value to return if the expression is false.
Example:
myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')
TRIM: Removes all leading and trailing blank characters like \r, \n, \t, ' '
TRIM(expression)
where: expression – The expression to trim.
SUBSTRING: Gets a sub-string of a specified length, starting at a specified point in the string.
SUBSTRING(expression, start, length)
where:
expression – The source string for the substring.
start – Integer that specifies where the substring starts.
length – Integer that specifies the length of the substring.
Example:
myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
Note: You can reset the Expression property by assigning it a null value or empty string. If a default value is set on the expression column, all previously filled rows are assigned the default value after the Expression property is reset.
Legal Notice | Contact Certara
© Certara USA, Inc. All rights reserved.