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.

Note:

Some parameters include descriptions that apply to the N/query module only.

Function	Description	Parameters	SuiteQL Examples
`CF`	Sets the field usage context to `CRITERIA`	Field name	`BUILTIN.CF(sales.item)`
`CONSOLIDATE`	Converts a currency amount stored in a field to a target currency	Original amount field name View type This parameter accepts the following values: `'LEDGER'` `'INCOME'` Consolidation rate type This parameter accepts the following values: `'DEFAULT'` `'STANDARD'` `'BUDGET'` Subsidiary rate type This parameter accepts the following values: `'DEFAULT'` `'CURRENT'` `'HISTORICAL'` `'AVERAGE'` `SQL EXPRESSION` Target subsidiary This parameter accepts the following values: Number SQL EXPRESSION Period This parameter accepts the following values: Number `SQL EXPRESSION` Book This parameter accepts the following values: `'DEFAULT'` `SQL EXPRESSION` To use the default value for any parameter (except original amount field name and view type), you can specify `'DEFAULT'` as the parameter value.	`BUILTIN.CONSOLIDATE(sales.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, NUMBER, 'DEFAULT')`
`CURRENCY`	Returns the currency code	Amount This parameter value can be any of the following types: `amount` `SUM(amount)` `BUILTIN.CONSOLIDATE` `SUM(BUILTIN.CONSOLIDATE)` `BUILTIN.CURRENCY_CONVERT` `SUM(BUILTIN.CURRENCY_CONVERT)`	`BUILTIN.CURRENCY(sales.amount)` `BUILTIN.CURRENCY( BUILTIN.CONSOLIDATE(sales.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, 304, 'DEFAULT') )`
`CURRENCY_CONVERT`	Converts a currency amount stored in a field to a target currency using the exchange rate that was in effect on a specific date	Original amount field name Target currency (optional; the default value is the default currency of the subsidiary) This parameter accepts the following values: `Number` `Date` Date of the exchange rate to use (optional; the default value is today’s date)	`BUILTIN.CURRENCY_CONVERT(sales.amount)` `BUILTIN.CURRENCY_CONVERT(sales.amount, 1)` `BUILTIN.CURRENCY_CONVERT(sales.amount, 1, TO_DATE('2019-01-30', 'YYYY-MM-DD'))`
`DF`	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, `sales.item`).	`BUILTIN.DF(sales.item)`
`HIERARCHY`	Returns the full hierarchical path to a value	Hierarchical field Expansion type This parameter accepts the following values (including the single quotation marks): `'DISPLAY'` `'DISPLAY_JOINED'` `'DISPLAY_SEPARATED'` `'IDENTIFIER'` `'IDENTIFIER_SEPARATED'` `'LEVEL'` `'SELF_DISPLAY'` `'SELF_IDENTIFIER'` Note: Hierarchy level can sometimes return varying results. For more information, see SuiteQL Limitations and Exceptions.	`BUILTIN.HIERARCHY(parent, 'IDENTIFIER_SEPARATED')`
`MNFILTER`	Filters a record to specify a target value on a multiselect field.	Relationship Field on Source Record Operator This parameter accepts the following values: `'MN_INCLUDE'` `'MN_INCLUDE_ALL'` `'MN_INCLUDE_EXACTLY'` `'MN_EXCLUDE'` `'MN_EXCLUDE_ALL'` `'MN_EXCLUDE_EXACTLY'` Display Field or an empty This parameter accepts the following values: `'DF'` ' ' boolean value value	`SELECT BUILTIN_RESULT.TYPE_INTEGER("ROLE"."ID") AS "ID" /{id#RAW}/,FROM"ROLE"WHERE BUILTIN.MNFILTER("ROLE".accessprofiles, 'MN_INCLUDE', '', 'TRUE', '-2') = 'T'`
`NAMED_GROUP`	Returns filter options for workgroups for some record types (department, class, location, subsidiary, and entity)	Record type Workgroup This parameter accepts the following values (including the single quotation marks): `'me'` – This value is supported for Entity, Customer, Vendor, and Partner record types. `'mine'` – This value is supported for Classification, Department, Location, and Subsidiary record types.	`BUILTIN.NAMED_GROUP('employee', 'me')` `BUILTIN.NAMED_GROUP('subsidiary', 'mine')` `select * from Subsidiary where id in BUILTIN.NAMED_GROUP('subsidiary', 'mine')`
`PERIOD`	Returns the contents of the IN predicate for a relative date range as a subselection	Range ID This parameter accepts the same property values listed in the query.RelativeDateRange enum in the N/query module (for example, `'LFY'` to represent a range starting or ending last fiscal year). Range type This parameter accepts the following values (including the single quotation marks): `'START'` `'END'` Adjustment This parameter accepts the following values (including the single quotation marks): `'NOT_LAST'` `'ALL'` Operator This parameter accepts the following values (including the single quotation marks): `'BETWEEN'` `'NOT BETWEEN'` `'<'` `'<='` `'>'` `'>='`	`BUILTIN.PERIOD('LFY', 'END', 'NOT_LAST', '>')` `BUILTIN.PERIOD('LFY', 'START', 'ALL', 'BETWEEN')`
`RELATIVE_RANGES`	Returns dynamic calendar ranges for the filter options that apply to relative date fields (such as Last Fiscal Year and Current Week)	Range ID This parameter accepts the same property values listed in the query.RelativeDateRange enum in the N/query module (for example, `'LFYTD'` to represent a range starting or ending last fiscal year to date). Range type This parameter accepts the following values (including the single quotation marks): `'START'` `'END'` Date flag (optional) This parameter accepts the following values (including the single quotation marks): `'DATE'` `'DATETIME_AS_DATE'` `'DATE_FROM_GMT_TO_USER_TZ'` `'DATE_TO_USER_TZ'` `'ODBC_DATE_TO_GMT'` `'ODBC_TIMESTAMP_TO_GMT'` `'ODBC_TIMESTAMP_TO_GMT_AS_DATE'` `'TIMESTAMP_WITH_TIMEZONE'` `'TIMESTAMP_WITH_TIMEZONE_AS_DATE'`	`BUILTIN.RELATIVE_RANGES('LFYTD', 'START')` `BUILTIN.RELATIVE_RANGES('LFYTD', 'END', 'DATETIME_AS_DATE')`

Function

Description

Parameters

SuiteQL Examples

CF

Sets the field usage context to CRITERIA

Field name

                    BUILTIN.CF(sales.item)

CONSOLIDATE

Converts a currency amount stored in a field to a target currency

Original amount field name
View type

This parameter accepts the following values:
- 'LEDGER'
- 'INCOME'
Consolidation rate type

This parameter accepts the following values:
- 'DEFAULT'
- 'STANDARD'
- 'BUDGET'
Subsidiary rate type

This parameter accepts the following values:
- 'DEFAULT'
- 'CURRENT'
- 'HISTORICAL'
- 'AVERAGE'
- SQL EXPRESSION
Target subsidiary

This parameter accepts the following values:
- Number
- SQL EXPRESSION
Period

This parameter accepts the following values:
- Number
- SQL EXPRESSION
Book

This parameter accepts the following values:
- 'DEFAULT'
- SQL EXPRESSION

To use the default value for any parameter (except original amount field name and view type), you can specify 'DEFAULT' as the parameter value.

                    BUILTIN.CONSOLIDATE(sales.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, NUMBER, 'DEFAULT')

CURRENCY

Returns the currency code

Amount

This parameter value can be any of the following types:

amount
SUM(amount)
BUILTIN.CONSOLIDATE
SUM(BUILTIN.CONSOLIDATE)
BUILTIN.CURRENCY_CONVERT
SUM(BUILTIN.CURRENCY_CONVERT)

                    BUILTIN.CURRENCY(sales.amount)

                    BUILTIN.CURRENCY( BUILTIN.CONSOLIDATE(sales.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, 304, 'DEFAULT')
)

CURRENCY_CONVERT

Converts a currency amount stored in a field to a target currency using the exchange rate that was in effect on a specific date

Original amount field name
Target currency (optional; the default value is the default currency of the subsidiary)

This parameter accepts the following values:
- Number
- Date
Date of the exchange rate to use (optional; the default value is today’s date)

                    BUILTIN.CURRENCY_CONVERT(sales.amount)

                    BUILTIN.CURRENCY_CONVERT(sales.amount, 1)

                    BUILTIN.CURRENCY_CONVERT(sales.amount, 1, TO_DATE('2019-01-30', 'YYYY-MM-DD'))

DF

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, sales.item).

                    BUILTIN.DF(sales.item)

HIERARCHY

Returns the full hierarchical path to a value

Hierarchical field
Expansion type

This parameter accepts the following values (including the single quotation marks):
- 'DISPLAY'
- 'DISPLAY_JOINED'
- 'DISPLAY_SEPARATED'
- 'IDENTIFIER'
- 'IDENTIFIER_SEPARATED'
- 'LEVEL'
- 'SELF_DISPLAY'
- 'SELF_IDENTIFIER'

Note:

Hierarchy level can sometimes return varying results. For more information, see SuiteQL Limitations and Exceptions.

                    BUILTIN.HIERARCHY(parent, 'IDENTIFIER_SEPARATED')

MNFILTER

Filters a record to specify a target value on a multiselect field.

Relationship Field on Source Record
Operator

This parameter accepts the following values:
- 'MN_INCLUDE'
- 'MN_INCLUDE_ALL'
- 'MN_INCLUDE_EXACTLY'
- 'MN_EXCLUDE'
- 'MN_EXCLUDE_ALL'
- 'MN_EXCLUDE_EXACTLY'
Display Field or an empty

This parameter accepts the following values:
- 'DF'
- ' '
boolean value
value

                    SELECT BUILTIN_RESULT.TYPE_INTEGER("ROLE"."ID") AS "ID" /*{id#RAW}*/,FROM"ROLE"WHERE BUILTIN.MNFILTER("ROLE".accessprofiles, 'MN_INCLUDE', '', 'TRUE', '-2') = 'T'

NAMED_GROUP

Returns filter options for workgroups for some record types (department, class, location, subsidiary, and entity)

Record type
Workgroup

This parameter accepts the following values (including the single quotation marks):
- 'me' – This value is supported for Entity, Customer, Vendor, and Partner record types.
- 'mine' – This value is supported for Classification, Department, Location, and Subsidiary record types.

                    BUILTIN.NAMED_GROUP('employee', 'me')

                    BUILTIN.NAMED_GROUP('subsidiary', 'mine')

                    select *
from Subsidiary
where id in BUILTIN.NAMED_GROUP('subsidiary', 'mine')

PERIOD

Returns the contents of the IN predicate for a relative date range as a subselection

Range ID

This parameter accepts the same property values listed in the query.RelativeDateRange enum in the N/query module (for example, 'LFY' to represent a range starting or ending last fiscal year).
Range type

This parameter accepts the following values (including the single quotation marks):
- 'START'
- 'END'
Adjustment

This parameter accepts the following values (including the single quotation marks):
- 'NOT_LAST'
- 'ALL'
Operator

This parameter accepts the following values (including the single quotation marks):
- 'BETWEEN'
- 'NOT BETWEEN'
- '<'
- '<='
- '>'
- '>='

                    BUILTIN.PERIOD('LFY', 'END', 'NOT_LAST', '>')

                    BUILTIN.PERIOD('LFY', 'START', 'ALL', 'BETWEEN')

RELATIVE_RANGES

Returns dynamic calendar ranges for the filter options that apply to relative date fields (such as Last Fiscal Year and Current Week)

Range ID

This parameter accepts the same property values listed in the query.RelativeDateRange enum in the N/query module (for example, 'LFYTD' to represent a range starting or ending last fiscal year to date).
Range type

This parameter accepts the following values (including the single quotation marks):
- 'START'
- 'END'
Date flag (optional)

This parameter accepts the following values (including the single quotation marks):
- 'DATE'
- 'DATETIME_AS_DATE'
- 'DATE_FROM_GMT_TO_USER_TZ'
- 'DATE_TO_USER_TZ'
- 'ODBC_DATE_TO_GMT'
- 'ODBC_TIMESTAMP_TO_GMT'
- 'ODBC_TIMESTAMP_TO_GMT_AS_DATE'
- 'TIMESTAMP_WITH_TIMEZONE'
- 'TIMESTAMP_WITH_TIMEZONE_AS_DATE'

                    BUILTIN.RELATIVE_RANGES('LFYTD', 'START')

                    BUILTIN.RELATIVE_RANGES('LFYTD', 'END', 'DATETIME_AS_DATE')

Related Topics