Functions

The Expression Builder dialog box in Integration Composer provides a set of predefined functions grouped into the following categories: text, SQL, math, mapping execution flow, mapping, logical, and date/time.

All functions

This feature lists all the Date/Time, Logical, Math, and Text Functions in the Integration Composer library.

Date/Time

The following table describes date/time functions available in the Expression Builder feature.

Function Description
currentDateString() Returns the current date as a string.
currentDayString() Returns the current day of the week as a string.
currentMonthString() Returns the current month as a string.
currentTimeString() Returns the current time as a string.
currentYearString() Returns the current year as a string.
dateString() Converts a numeric date to a string.
mappingTimestampString() Returns the mapping execution start time as a string (YYYY-MM-dd-HH.mm.ss.SSSSSS).

Logical

The following table describes logical functions available in the Expression Builder feature.

Function Description
ifDecimal Based on the conditional being true or false, returns either the first or second specified decimal value.
ifInt Based on the conditional being true or false, returns either the first or second specified integer value.
ifString Based on the conditional being true or false, returns either the first or second specified string value.
logicalAnd Returns true if both parameters are true.
logicalFalse() Returns false.
logicalNot Returns the Boolean opposite of the parameter.
logicalOr Returns true if either parameter is true.
logicalTrue() Returns true.

Mapping

The following table describes mapping functions available in the Expression Builder feature.

Function Description
getMappingStartTimestamp() Returns the mapping start time stamp as a string.
getSourceDataSchemaName() Returns the data schema name of the source data source as a string.
getSourceDataSourceName() Returns the name of the source data source as a string.
getTargetDataSchemaName() Returns the data schema name of the target data source as a string.
getTargetDataSourceName() Returns the name of the target data source as a string.
getSourceTableOwner() Returns the name of the table owner for the source data source as a string
getTargetTableOwner() Returns the name of the table owner for the target data source as a string

Math

The following table describes math functions available in the Expression Builder feature.

Function Description
decimalToString Returns a string representation of a decimal value.
Example
decimalToString(5.3)
     returns
a string made of the characters "5", ".", and "3"
decimalabs Returns the absolute value of a decimal value 
Example
decimalabs(-15.5)
     returns
15.5
decimalmax Returns the greater of 2 decimal values. 
Example
decimalmax(`CURRVALUE',`CLOSINGVALUE')
     returns
the greater value of properties CURRVALUE and CLOSINGVALUE
decimalmin Returns the lesser of 2 decimal values. 
Example
decimalmin(`CURRVALUE',`CLOSINGVALUE')
     returns
the lesser value of properties CURRVALUE and CLOSINGVALUE
intToString Returns a string representation of an integer value. 
Example
intToString(14)
     returns
a string made of the characters "1", and "4"
intabs Returns the absolute value of an integer value. 
Example
intabs(-4)
     returns
4
intmax Returns the greater of 2 integer values. 
Example
intmax(`NUMHIRED',`NUMFIRED')
     returns
the greater value of properties NUMHIRED and NUMFIRED
intmin Returns the lesser of 2 integer values. 
Example
intmin(`NUMHIRED',`NUMFIRED')
     returns
the lesser value of properties NUMHIRED and NUMFIRED

SQL

The following table describes SQL statement functions available in the Expression Builder feature.

Use of SQL functions might adversely affect mapping execution performance.

Function Description
executeSourceSQL logicalFalse()

Returns the first column of the first row as a string of an SQL statement's results of source data source.

Example
Execute an SQL statement on the source database
executeTargetSQL Returns the first column of the first row as a string of an SQL statement's results of target data source. 
Example
Execute an SQL statement on the target database

Integration Composer executes these SQL statements exactly as written. If the database is case-sensitive, format the SQL statement to use the correct case.

If a query returns multiple rows, only the first row is used in a mapping. If a query returns multiple columns, only the first column is used in a mapping.

Text

The following table describes text functions available in the Expression Builder feature.

Function Description
compare Returns an integer value less than, equal to, or greater than 0 based on the equality of the specified strings.
endsWith(string, suffix) Returns true if the character sequence represented by the argument is a suffix of the character sequence represented by this string; false otherwise
equals(string1, string2) Returns true if the two strings contain the same characters in the same sequence; false otherwise. The comparison is case-sensitive.
equalsIgnoreCase(string1, string2) Returns true if the two strings contain the same characters in the same sequence; false otherwise. The comparison is not case sensitive.
indexOf Returns the index within this string of the first occurrence of the specified substring. 
Example
indexOf(`CONTACT', "FAX")
     returns
the first index of the string FAX in the property CONTACT

If Integration Composer does not find the string FAX in the property CONTACT, it returns -1.

Example
indexOf("this a string", "FAX")
     returns
-1

Example
indexOf("10.3.2, ".")
     returns
2
isNull(string) Returns true if the string is null or if the trimmed string is empty (""); false otherwise.
lastIndexOf Returns the index within this string of the last occurrence of the specified substring. 
Example
lastindexOf(`CONTACT', "FAX")
     returns
the last index of the string FAX in the property CONTACT

If Integration Composer does not find the string FAX in the property CONTACT, it returns -1.

Example
lastindexOf("10.3.2, ".")
     returns
4
length Returns the length (number of characters) of a string.
replaceString Replaces a substring of a string with a new string. 
Example
replaceString("Microsoft® Windows® Operating System",
"®", "(R)")
    returns
Microsoft(R) Windows(R) Operating System
startsWith Returns true if the character sequence represented by the argument is a prefix of the character sequence represented by this string; false otherwise. 
Example
startsWith(`NODE', "HR")
     returns
true if the property NODE starts with the string HR
stringToAscii Removes all non-ASCII characters from a string. 
Example
stringToAscii("Instala≤≤o")
     returns
Instalao
stringToDecimal Converts a string value to a decimal.
stringToInt Converts a string value to an integer.
subString Returns a portion of a string from beginIndex (inclusive) to endIndex (exclusive). 
Example
subString("FIELD 1",0,2)
     returns
the first two characters of FIELD 1
toLowerCase Converts all letters in a string to lowercase. 
Example
toLowerCase("FIELD 1")
     returns
field 1
toUpperCase Converts all letters in a string to uppercase. 
Example
toUpperCase("FIELD 1")
     returns
FIELD 1
trim Removes leading and trailing white space (tab, space, and so on) from both ends of the string. 
Example
trim("  CELL NAME   ")
     returns
CELL NAME
setToNull(String attributeName) By default, primitive attribute types (int, long, float, and so on) default to 0 or 0.0 in the target database if a mapping expression is not provided. This default is problematic when a 0 value means something in target database.

You cannot add a `return null;' to an expression because a primitive type is expected, and the Java compilation fails. This function enables you to avoid this problem:

For example, you want to set the Supportssnmp attribute to null:

{
setToNull("Supportssnmp");
return 0;
}

The attribute name is case-sensitive. The function can be called in any of the class instance attribute expressions. It must still return the correct primitive type.
Parent topic: Expression functions, literals, and operators

Feedback