SuiteQL Supported Built-in Functions
You can use built-in functions to perform certain operations in SuiteQL queries. These functions extend the capabilities that are provided by the SQL-92 specification. For example, you can use the CONSOLIDATE
built-in function to convert a currency amount stored in a field to a target currency.
To use a built-in function, you must add BUILTIN.
before the function name. For example, to call the CONSOLIDATE
built-in function, you must use BUILTIN.CONSOLIDATE
in your SuiteQL query.
The following table describes the built-in functions that are supported in SuiteQL and provides examples of each function. Some parameters are optional and are documented as such in the table, but all other parameters are required.
Some parameters include descriptions that apply to the N/query module only.
Function |
Description |
Parameters |
SuiteQL Examples |
---|---|---|---|
|
Sets the field usage context to |
Field name |
|
|
Converts a currency amount stored in a field to a target currency |
To use the default value for any parameter (except original amount field name and view type), you can specify |
|
|
Returns the currency code |
Amount This parameter value can be any of the following types:
|
|
|
Converts a currency amount stored in a field to a target currency using the exchange rate that was in effect on a specific date |
|
|
|
Returns the display value of a field from the target record type without having to join the target record type explicitly |
Field name This field name represents the relationship between the field and the source record type (for example, |
|
|
Returns the full hierarchical path to a value |
Note:
Hierarchy level can sometimes return varying results. For more information, see SuiteQL Limitations and Exceptions. |
|
|
Filters a record to specify a target value on a multiselect field. |
|
|
|
Returns filter options for workgroups for some record types (department, class, location, subsidiary, and entity) |
|
|
|
Returns the contents of the IN predicate for a relative date range as a subselection |
|
|
|
Returns dynamic calendar ranges for the filter options that apply to relative date fields (such as Last Fiscal Year and Current Week) |
|
|