Function attribute properties

Overview

The following initial interface is displayed when creating a function attribute.

The interface is used to select the function upon which the attribute is to be based.

If creating a function attribute at an RPI tenant in which one or more auxiliary databases has been configured, you must first choose the Database in respect of which to create the function attribute.

Choose the database… is not displayed if creating a function attribute at a tenant at which no auxiliary databases have been configured.

The list of functions available to you when building a function attribute differs based on the type of database in respect of which the attribute is to be created.

AWS Redshift

Date & time functions

Function name	Description
Add Months	Adds the specified number of months to a given date expression
Date Compare	Returns an integer. 0 if identical, -1 if first date is less than second date & 1 if first date is greater than second date
DateTime Compare Date	Returns an integer. 0 if identical, -1 if datetime is less than date and 1 if datetime is greater than date
Date Compare DateTime	Returns an integer. 0 if identical, -1 if date is less than datetime and 1 if first date is greater than datetime
Year	Returns the year of the specified date expression
Date Add	Returns the datetime
Date Difference	Returns the difference between datetime expressions
Date Part	Returns decimal number represents the datepart
Date Truncate	Returns datetime
Extract	Returns the datepart
Interval Compare	Returns an integer. 0 if identical, -1 if first interval is less than second interval and 1 if first interval is greater than second interval
Isfinite DateTime	Returns Boolean
Add Months	Adds the specified number of months to a given date expression

String functions

Function name	Description
Absolute	Returns the absolute value of the specified numeric expression
Ceiling	Returns the ceiling value of the specified numeric expression
Floor	Returns the ceiling value of the specified numeric expression
Exponent	Returns the exponential value of the specified numeric expression
Power	Returns the exponential value of the specified numeric expression raised by the second numeric expression
Round	Rounds numbers to the nearest integer or decimal

Math functions

Function name	Description
Length	Returns the length of the specified text
Quote Identifier	Returns equivalent quoted identifier
Quote Literal	Returns equivalent quoted literal
Repeat	Repeats text the specified number of times
Replace	Returns all occurrences of the replaced text
Right Trim	Returns the trimmed text
Reverse	Returns the reversed text
String Position	Returns integer representing the position of the second text in the first text expression
Substring	Returns the extracted text from the specified text expression
Trim	Returns the trimmed text

Azure Database for MySQL

Date & time functions

Function name	Description
Add days	Adds a number of days to date.
Add months	Adds a number of months to date.
Date Add	Adds an interval to a date.
Date difference	Returns the interval between two dates.

String functions

Function name	Description
Left	Return the leftmost number of characters as specified.
Left pads	Returns a character string of the desired length after padding the existing string to the left.
Left trim blanks	Returns a character expression after removing leading blanks.
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case	Returns a character expression after converting upper case character data to lower case.
Position	Returns the location of a substring in a string.
Right	Return the rightmost number of characters as specified.
Right pads	Returns a character string of the desired length after padding the existing string to the right.
Right trim blanks	Returns a character expression after removing trailing blanks.
Substring	Returns parts of a character, binary, text, or image expression.
Trim blanks	Returns a character expression after removing leading and trailing blanks.
Upper case	Returns a character expression after converting lower case character data to upper case.

Math functions

Function name	Description
Absolute	Returns the absolute, positive value of the given numeric expression.
Ceiling	Returns the smallest integer greater that, or equal to, the given numeric expression.
Exponential	Returns the exponential value of the given float expression.
Floor	Returns the largest integer less than or equal to the given numeric expression.
Random	Returns a random integer value.

Azure Database for PostgreSQL

Date & time functions

Function name	Description
Date Part	Return the part of a data and time.

Function name	Description
Length	Returns the number of characters in string.
Position	Return the location of a substring within a specified string.
Left	Return the n number of characters specified in the argument from the left of a given string.
Right	Return the n number of characters specified in the argument from the right of a given string.
Substring	Returns the location of a substring in a string.
Lower	Return the string to lower case.
Upper	Return the string to upper case.
Trim	Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string.
Ltrim	Remove the longest string containing only characters from characters (a space by default) from the start of string.
Rtrim	Remove the longest string containing only characters from characters (a space by default) from the end of string.
Replace	Replace all occurrences in string of substring from with substring to.
Strpos	Location of specified substring (same as position (substring in string), but note the reversed argument order).

Math functions

Function name	Description
Absolute	Returns the absolute, positive value of the given numeric expression.
Ceil	Return the nearest integer greater than or equal to argument.
Ceiling	Return the nearest integer greater than or equal to argument (same as ceil).
Power	Returns the exponential value of a numeric expression raised to the specified power.
Floor	Returns the largest integer less than or equal to the given numeric expression.

Azure SQL Database

Date & time functions

Function name	Description
Date add	Returns a new datetime value based on adding an interval to the specified date.
Date difference	Returns the number of date and time boundaries crossed between two specified dates.
Date part	Returns an integer representing the specified datepart of the specified date.
Day	Returns an integer representing the day datepart of the specified date.
Month	Returns an integer representing the month datepart of the specified date.
To string	Converts a date or datetime value into a string in accordance with the specified format.
Year	Returns an integer representing the year datepart of the specified date.

Math functions

Function name	Description
Absolute	Returns the absolute, positive value of the given numeric expression.
Ceiling	Returns the smallest integer greater than, or equal to, the given numeric expression.
Exponential	Returns the exponential value of the given float expression.
Floor	Returns the largest integer less than or equal to the given numeric expression.
Random	Returns a random integer value.

String functions

Function name	Description
Difference	Returns the difference between the SOUNDEX values of two character expressions as an integer.
Left	Returns the left part of a character string with the specified number of characters.
Left trim	Returns a character expression after removing blanks.
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case	Returns a character expression after converting upper case character data to lower case.
Replace	Deletes a specified length of characters and inserts another set of characters at a specified starting point.
Reverse string	Returns the reverse of a character expression.
Right	Returns the right part of a character string with the specified number of characters.
Right trim	Returns a character string after truncating all trailing blanks.
String concatenation (2 strings)	Concatenates two string values.
String concatenation (3 strings)	Concatenates three string values.
String concatenation (4 strings)	Concatenates four string values.
String concatenation (5 strings)	Concatenates five string values.
SubString	Returns part of a character, binary, text, or image expression.
Upper case	Returns a character expression after converting lower case character data to upper case.

Databricks

Date & time functions

Function name	Description
DateAdd	Returns the number of date boundaries crossed between two specified dates
DateAdd2	Add number of days to start date
Date difference	Returns the number of date boundaries crossed between two specified dates
Date difference 2	Returns the number of date boundaries crossed between two specified dates based on type of boundary
DateSub	Subtract number of days to start Date
Day of Month	returns day part of a date
Hour	returns hour part of a date
Minute	returns minute part of a date
Month	returns month part of a date
Second	returns second part of a date
Unix Timestamp	convert date to unix timestamp
Week of year	returns week number of date
Year	returns year part of date

Math functions

Function name	Description
Absolute	returns the absolute value
Ceil	returns the ceiling value
Exponential	returns the exponential value
Floor	returns the floor value
Power	returns the power of the base value
Random	returns random number based on the specified seed
Round	returns the rounded value
RoundTwo	returns the rounded value

String functions

Function name	Description
Concatenate	combines first and second string value
Concatenate with separator	combines first and second string value to a specified separator
Instring	returns the position of the searched string
Left padding	returns left-padded string
Left trim	returns the string resulted from trimming spaces from the left.
Length	returns the length of the string
Locate	returns the position of the searched string
Lowercase	returns the lower case equivalent of the string
Repeat	returns the repeated string
Reverse	returns the reversed string
Right padding	returns right-padded string
Right trim	returns the string resulted from trimming spaces from the right.
Space	returns the number of spaces generated
Substring	returns the extracted string
Substring2	returns the extracted string
Trim	returns the string resulted from trimming spaces from both sides
Uppercase	returns the upper case equivalent of the string

Google BigQuery

Date & time functions

Function name	Description
Datetime difference	Returns the number of whole specified part intervals between two Datetime objects.
Extract	Returns the value corresponding to the specified date part.
Datetime Add	Returns a new datetime value based on adding an interval to the specified date.

String functions

Function name	Description
Character Length	Returns the length of the STRING in characters.
Left Trim	Returns a character expression after removing leading blanks.
Right Trim	Returns a character string after truncating all trailing blanks.
Trim	Removes all leading and trailing characters that match value2.
Lowercase	Returns a character expression after converting upper case character data to lower case.
Uppercase	Returns a character expression after converting lower case character data to upper case.
Reverse string	Returns the reverse of a character expression.
SubString(position and length)	Returns a substring of the supplied value of position and length.
Substring	Returns a substring of the supplied value.
Replace	Replaces all occurrences of from_value with to_value in original_value.
String concatenation (2 strings)	Concatenates two string values.

Math functions

Function name	Description
Absolute	Returns the absolute value of a double value.
Exponential	Returns the exponential value of the given float expression.
Ceiling	Returns the smallest integer that is not less than the value.
Power	Returns e raised to the power of b.

Google BigTable

Date & time functions

Function name	Description
Datetime difference	Returns the number of whole specified part intervals between two Datetime object.
Extract	Returns the value corresponding to the specified date part.

Math functions

Function name	Description
Absolute	Returns the absolute value of a double value.
Ceiling	Returns the smallest integer that is not less than the value.
Exponential	Returns the exponential value of the given float expression.
Power	Returns a raised to the power of b.

String functions

Function name	Description
Character Length	Returns the length of the STRING in Characters.
Left trim	Returns a character expression after removing leading blanks.
Lower case	Returns a character expression after converting upper case character data to lower case.
Replace	Replaces all occurrences of from value with to value in original value.
Reverse string	Returns the reverse of a character expression.
Right trim	Returns a character string after truncating all trailing blanks.
String concatenation (2 strings)	Concatenates two string value.
SubString	Returns a substring of the supplied value.
SubString (position and length)	Returns a substring of the supplied value of position and length.
Trim	Removes all leading and trailing characters that match value2.
Upper case	Returns a character expression after converting lower case character data to upper case.

PostgreSQL

Date & time functions

Function name	Description
Date truncate	Returns a truncated timestamp to a specified precision.
Extract	Returns the extracted timestamp.
Date Part	Returns the date part of the given date and time expression.
Year	Returns the Year part of the given date and time expression.
Month	Returns the Month part of the given date and time expression.
Day	Returns the day part of the given date and time expression.

String functions

Function name	Description
Length	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case	Returns a character expression after converting upper case character data to lower case.
Upper case	Returns a character expression after converting lower case character data to upper case.
InitCap	Returns a character expression after capitalizing the initial letter.
Left	Returns the left part of a character string with the specified number of characters.
Right	Returns the right part of a character string with the specified number of characters.
Right trim	Remove all characters from right position of a specified character expression.
Left trim	Remove all characters from left position of a specified character expression.
Substring	Returns a character expression based on the specified starting position and length.
Concatenate	Concatenates string expressions.

Math functions

Function name	Description
Absolute	Returns the absolute (positive) value.
Floor	Returns the largest integer not greater than given argument.
Ceiling	Returns the smallest integer not less than the given argument.
Power	Returns the exponential value of a numeric expression raised to the specified power.
Exp	Returns e raised to the power of the specified number.

SQL Server

Date & time functions

Function name	Description
Date Add(Date Part, Number of Date Parts to Add, Date)	Returns a new datetime value based on adding an interval to the specified date.
Date Difference(Date Part, Start Date, End Date)	Returns the number of date and time boundaries crossed between two specified dates.
Date Part(Date Part, Date)	Returns an integer representing the specified date part of the specified date.
Day(Date)	Returns an integer representing the day date part of the specified date.
Month(Date)	Returns an integer representing the month date part of the specified date.
To string (Date)	Converts a date or datetime value into a string in accordance with the specified format (see http://msdn.microsoft.com/en-us/library/ms187928.aspx for a list of valid formats).
Year(Date)	Returns an integer representing the year date part of the specified date.

Math functions

Function name	Description
Absolute(Number)	Returns the absolute, positive value of the given numeric expression.
Ceiling(Number)	Returns the smallest integer greater than, or equal to, the given numeric expression.
Exponential(Number)	Returns the exponential value of the given float expression.
Floor(Number)	Returns the largest integer less than or equal to the given numeric expression.
Random(Number, Number)	Returns a random integer value.

String functions

Function name	Description
Difference(String, String)	Returns the difference between the SOUNDEX values of two character expressions as an integer.
Left(String, Number of Characters)	Returns the left part of a character string with the specified number of characters.
Left trim(String)	Returns a character expression after removing leading blanks.
Length(String)	Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
Lower case(String)	Returns a character expression after converting uppercase character data to lowercase.
Replace(Original String, Start Position, Length, String to Insert)	Deletes a specified length of characters and inserts another set of characters at a specified starting point.
Reverse string(String)	Returns the reverse of a character expression.
Right(String, Number of Characters)n	Returns the right part of a character string with the specified number of characters.
Right trim(String)	Returns a character string after truncating all trailing blanks.
String concatenation (2 strings)	Concatenates two string values.
String concatenation (3 strings)	Concatenates three string values.
String concatenation (4 strings)	Concatenates four string values.
String concatenation (5 strings)	Concatenates five string values.
SubString(String, Start Position, Length)	Returns part of a character, binary, text, or image expression.

Having selected a function, a second function attribute-specific interface is shown (note that this interface is displayed immediately if configuring a function attribute; the information panel shown to the right is only displayed when configuring an existing attribute):

The interface is used to configure the following specific properties:

Target Table: you must browse for a Target table using the Choose Database Item dialog. A list of tables within the currently specified database context is shown. Select the required table and select OK. For more information on using the Choose Database Item dialog, please see the RPI Framework documentation.
Data Length: only displayed for function attributes that return a string value, this property allows you to define the attribute’s maximum data length. It defaults to 30.
Arguments list: the specific arguments to be provided are dependent on the function chosen. The function's argument signature is displayed to the right of the interface. For each argument, the following columns are displayed:
- Index: (read-only) a zero-based number that refers to the ordinal position of the argument within the function - note that a summary of the function is displayed to the top right of the interface, and index is used to indicate the position of the argument within the function.
- Value: (updateable): for each argument in a function, you can specify whether its value is to be supplied as an attribute or constant. The default is attribute. You can toggle between Attribute and Constant.
  If Constant is selected, you can type a constant value directly into the updateable field.
  If Attribute is selected, you can drag and drop an attribute from the toolbox or browse for an attribute using the recent items chooser or File System Dialog. Note that you cannot:
  - Use a model project, exists in table or parameter attribute in this context.
  - Configure a function attribute using itself.
  - Select an attribute with a Target table from a database different to the new function attribute’s Target table’s database.In addition, if an argument is of a date/time data type, you can also specify that its value be the ‘Current Time’. If used when configuring a function attribute, the attribute cannot be used to build a list comparison criterion.
    Note that if a value with an invalid data type is supplied for the argument, a warning message is displayed.
    You can also view the File Information Dialog if an attribute is specified.
- Data type: (read-only) one of: string, integer, decimal, date/time.
- Description: (read-only) a textual description of the role played by the argument within the function.