Global constraints

Pentaho Metadata Editor

Version
9.5.x
Audience
anonymous
Part Number
MK-95PDIA007-02

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 parameters are null, the message in the last parameter is returned.
DATEMATH One expression Returns a date value based on a DATEMATH expression (see DateMath Javadoc for full syntax).

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.