Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

MQL Formula Syntax For Global Constraints

  1. Last updated
  2. Save as PDF

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)

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.