MQL Formula Syntax
You can apply global or user and role row-level constraints using MQL.
Global Constraints
You can use all of the standard operators, and any of the following functions when defining a global constraint:
| Function Name | Parameters | Description |
|---|---|---|
| OR | Two or more boolean expressions | Returns true if one or more parameters are true |
| AND | Two or more boolean expressions | Returns true if all parameters are true |
| LIKE | Two | Compares a column to a regular expression, using % as a wild card |
| IN | Two or more | Checks to see if the first parameter is in the following list of parameters |
| NOW | N/A | The current date |
| DATE | Three numeric parameters: Year, month, and day | The specified date |
| DATEVALUE | One text parameter: year-month-day | The specified date |
| CASE | Two or more | Evaluates the odd-numbered parameters, and returns the even numbered parameter values. If there are an odd number of parameters, the last parameter is returned if no other parameter evaluates to true. |
| COALESCE | One or more | Returns the first non-null parameter. If all are null, the message in the last parameter is returned. |
| DATEMATH | One expression | Returns a date value based on a DATEMATH expression (see related links below for a link to the DATEMATH Javadoc) |
The following table contains examples of the functions:
| Function Name | Example |
|---|---|
| OR |
OR( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )
|
| AND |
AND( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars";
[BT_CUSTOMERS.BC_CUSTOMERS_CREDITLIMIT] > 1000 )
|
| LIKE |
LIKE([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "%SMITH%") |
| IN |
IN([BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME]; "Adam Smith"; "Brian Jones") |
| NOW |
NOW() |
| DATE |
DATE(2008;4;15) |
| DATEVALUE |
DATEVALUE("2008-04-15")
|
| CASE |
CASE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "EuroCars"; "European Cars";
[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME] = "AsiaCars"; "Asian Cars"; "Unknown Cars")
|
| COALESCE |
COALESCE( [BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERNAME];
[BT_CUSTOMERS.BC_CUSTOMERS_CUSTOMERID]; "Customer is Null" )
|
| DATEMATH |
DATEMATH("0:ME -1:DS")
This expression represents 00:00:00.000 on the day before the last day of the current month. |
User and Role Row-Level Constraints
The MQL Formula syntax for defining a user or role row constraint is:
[table.column] = "row"
The table and column are defined as part of a metadata business model. Here is an example that isolates access to data from the Sales department:
[BT_OFFICE.BC_DEPARTMENT]="Sales"
It's also possible to give or deny access to an entire role, or a single user, by selecting that user or role, then using a TRUE() or FALSE() boolean for a constraint.