Function attribute properties
Overview
The following initial interface is displayed when creating a function attribute.
![](../__attachments/3904935740/att_3_for_3861053740.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
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.
![](../__attachments/3904935740/att_1_for_3861053740.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
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):
![](../__attachments/3904935740/att_2_for_3861053740.png?inst-v=c3007bf4-8f91-43d3-9633-90859273c945)
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.