NetSuiteFree referencePDF, Excel, Word exportNetSuiteAll NetSuite tools

NetSuite SuiteQL BUILTIN Functions Reference

Oracle documents exactly ten BUILTIN functions for SuiteQL; this table gives each one with its parameters in order, an example you can paste and the allowed values. Below them are the Oracle SQL functions developers use most in SuiteQL queries.

Download:

Group	Function	Purpose	Signature (parameters in order)	Example	Returns	Notes
BUILTIN	BUILTIN.CF	Criteria value of a field with combined values, such as transaction status	BUILTIN.CF(field)	WHERE BUILTIN.CF(t.status) = 'SalesOrd:B'	Type and status combined, for example 'CustInvc:B'	Oracle says to use it in filters on composite key fields to avoid wrong results.
BUILTIN	BUILTIN.CONSOLIDATE	Convert an amount to a target subsidiary currency using consolidation rates	BUILTIN.CONSOLIDATE(amount_field, view_type, consolidation_rate_type, subsidiary_rate_type, target_subsidiary, period, book)	BUILTIN.CONSOLIDATE(tal.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, t.postingperiod, 'DEFAULT')	Converted amount	view_type 'LEDGER' or 'INCOME'. Rate type 'DEFAULT', 'STANDARD' or 'BUDGET'. Subsidiary rate 'DEFAULT', 'CURRENT', 'HISTORICAL', 'AVERAGE' or an expression. Book 'DEFAULT' or an expression.
BUILTIN	BUILTIN.CURRENCY	Return the currency code of an amount	BUILTIN.CURRENCY(amount)	BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(tal.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, t.postingperiod, 'DEFAULT'))	Currency code	Accepts amount, SUM(amount), BUILTIN.CONSOLIDATE or BUILTIN.CURRENCY_CONVERT and their SUM.
BUILTIN	BUILTIN.CURRENCY_CONVERT	Convert an amount to a target currency using the exchange rate effective on a date	BUILTIN.CURRENCY_CONVERT(amount_field [, target_currency_id [, rate_date]])	BUILTIN.CURRENCY_CONVERT(tl.foreignamount, 1, TO_DATE('2026-09-30', 'YYYY-MM-DD'))	Converted amount	Target defaults to the subsidiary currency; rate date defaults to today, so pass the transaction date for historic reports.
BUILTIN	BUILTIN.DF	Display value of a reference field without joining the target table	BUILTIN.DF(field)	SELECT BUILTIN.DF(t.status), BUILTIN.DF(tl.item) FROM transaction t JOIN transactionline tl ON tl.transaction = t.id	Display text	The function you will use most. Works on list, record and status fields, including custom ones.
BUILTIN	BUILTIN.HIERARCHY	Full hierarchical path of a value such as a parent item, department or account	BUILTIN.HIERARCHY(hierarchical_field, expansion_type)	SELECT id, parent, BUILTIN.HIERARCHY(parent, 'DISPLAY_JOINED') FROM item	Path as ids, names or level depending on expansion type	'DISPLAY_JOINED' returns the joined name path. Other expansion types return ids or the level; take the exact list from the BUILTIN.HIERARCHY help page for your release.
BUILTIN	BUILTIN.MNFILTER	Filter on a multiple-select field	BUILTIN.MNFILTER(relationship_field, operator, display_field_or_empty, case_sensitive, value)	WHERE BUILTIN.MNFILTER(role.accessprofiles, 'MN_INCLUDE', '', 'TRUE', '-2') = 'T'	Used as a T/F test in WHERE	Operators: 'MN_INCLUDE', 'MN_INCLUDE_ALL', 'MN_INCLUDE_EXACTLY', 'MN_EXCLUDE', 'MN_EXCLUDE_ALL', 'MN_EXCLUDE_EXACTLY'. Third argument 'DF' to filter on display text, '' to filter on ids.
BUILTIN	BUILTIN.NAMED_GROUP	Records that belong to the current user: me or mine	BUILTIN.NAMED_GROUP(record_type, workgroup)	SELECT * FROM subsidiary WHERE id IN BUILTIN.NAMED_GROUP('subsidiary', 'mine')	A set of ids for an IN clause	'me' for entity types (employee, customer, vendor, partner); 'mine' for classification, department, location and subsidiary.
BUILTIN	BUILTIN.PERIOD	Accounting periods for a relative period range, as an IN sub-select	BUILTIN.PERIOD(range_id, range_type, adjustment, operator)	WHERE t.postingperiod IN BUILTIN.PERIOD('LFY', 'START', 'ALL', 'BETWEEN')	Contents of an IN predicate (period ids)	range_type 'START' or 'END'; adjustment 'NOT_LAST' or 'ALL'; operator 'BETWEEN', 'NOT BETWEEN', '<', '<=', '>', '>='. Range IDs come from the query.RelativeDateRange list.
BUILTIN	BUILTIN.RELATIVE_RANGES	Start or end date of a relative date range such as last fiscal year	BUILTIN.RELATIVE_RANGES(range_id, range_type [, date_flag])	WHERE t.trandate >= BUILTIN.RELATIVE_RANGES('LFY', 'START') AND t.trandate <= BUILTIN.RELATIVE_RANGES('LFY', 'END')	A date (or timestamp, depending on date_flag)	range_type 'START' or 'END'. The optional third parameter controls the returned data type; check the help page before using it. 'LFY' is last fiscal year, 'LFYTD' last fiscal year to date.
Oracle SQL functions supported	NVL	Replace NULL with a default	NVL(expr, default)	NVL(tl.quantityshiprecv, 0)	expr or default	Use before arithmetic; NULL plus a number is NULL.
Oracle SQL functions supported	NVL2	One value when not NULL, another when NULL	NVL2(expr, if_not_null, if_null)	NVL2(t.duedate, 'Has terms', 'No due date')	Either branch	Both branches must be the same data type.
Oracle SQL functions supported	COALESCE	First non-NULL of several values	COALESCE(expr1, expr2, ...)	COALESCE(tl.memo, t.memo, '')	First non-NULL value	Cleaner than nested NVL.
Oracle SQL functions supported	CASE	Conditional logic	CASE WHEN cond THEN a [WHEN ...] ELSE b END	CASE WHEN t.status = 'H' THEN 'Closed' ELSE 'Open' END	Branch value	Prefer it over DECODE for readability.
Oracle SQL functions supported	DECODE	Map values in a compact form	DECODE(expr, search1, result1, ..., default)	DECODE(t.type, 'SalesOrd', 'Order', 'CustInvc', 'Invoice', 'Other')	Mapped value	Oracle-specific; treats two NULLs as equal.
Oracle SQL functions supported	TO_CHAR	Format a date or number as text	TO_CHAR(value, format)	TO_CHAR(t.trandate, 'YYYY-MM')	Text	Good for month buckets in GROUP BY.
Oracle SQL functions supported	TO_DATE	Text to date	TO_DATE(text, format)	t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')	Date	Always pass the format; account date preferences differ.
Oracle SQL functions supported	TRUNC	Cut a date to the day, month or year (or a number to n decimals)	TRUNC(date [, unit]) or TRUNC(number [, n])	TRUNC(t.createddate)	Date or number	Use on timestamp columns before comparing with dates.
Oracle SQL functions supported	ADD_MONTHS	Date plus or minus months	ADD_MONTHS(date, n)	t.trandate >= ADD_MONTHS(TRUNC(SYSDATE), -12)	Date	Negative n goes back in time.
Oracle SQL functions supported	SYSDATE	Current date and time	SYSDATE	TRUNC(SYSDATE) - t.trandate	Date	Subtracting dates gives days as a number.
Oracle SQL functions supported	ROUND	Round a number	ROUND(number [, n])	ROUND(tl.rate * ABS(tl.quantity), 2)	Number	Also rounds dates when given a date.
Oracle SQL functions supported	SUBSTR	Part of a string	SUBSTR(text, start [, length])	SUBSTR(i.itemid, 1, 3)	Text	Positions start at 1.
Oracle SQL functions supported	INSTR	Position of a substring	INSTR(text, search [, start [, occurrence]])	INSTR(i.itemid, '-')	Number, 0 if not found	Combine with SUBSTR to split part number prefixes.
Oracle SQL functions supported	ROWNUM	Limit rows (SuiteQL has no LIMIT keyword)	WHERE ROWNUM <= n	SELECT * FROM (SELECT id, tranid FROM transaction ORDER BY id DESC) WHERE ROWNUM <= 50	Row number pseudo column	Apply it outside a sub-select when you also ORDER BY. For paging use runSuiteQLPaged in N/query or limit and offset on the REST suiteql endpoint.

BUILTIN functions and parameter values follow Oracle help 'SuiteQL Supported Built-in Functions' and the page for each function. Parameters are required unless shown in square brackets. SuiteQL accepts SQL-92 and Oracle SQL syntax but has no LIMIT keyword. Test any other Oracle function in your own account before relying on it.

Stuck on a currency or period query?

Send us the SuiteQL and the report it must tie to, and we will check the BUILTIN usage and rate choice.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by function name or by the job, such as currency, period, hierarchy or display name. Every BUILTIN call needs the BUILTIN. prefix; DF alone fails. String arguments are in single quotes and are case sensitive, so 'LEDGER' and 'ledger' are not the same. Optional parameters are shown in square brackets; everything else is required, and for CONSOLIDATE you may pass 'DEFAULT' for any parameter except the amount and the view type.

Labels without joins: BUILTIN.DF
Status filters: BUILTIN.CF
Currency: CURRENCY_CONVERT for transaction rates, CONSOLIDATE for consolidation rates
Relative dates: RELATIVE_RANGES for dates, PERIOD for accounting periods

Worked example: last fiscal year revenue by customer in one currency

SELECT BUILTIN.DF(t.entity) AS customer, SUM(BUILTIN.CURRENCY_CONVERT(tl.foreignamount, 1, t.trandate)) * -1 AS revenue FROM transaction t JOIN transactionline tl ON tl.transaction = t.id WHERE t.type = 'CustInvc' AND tl.mainline = 'F' AND tl.taxline = 'F' AND t.trandate BETWEEN BUILTIN.RELATIVE_RANGES('LFY', 'START') AND BUILTIN.RELATIVE_RANGES('LFY', 'END') GROUP BY BUILTIN.DF(t.entity). The -1 flips the negative revenue sign on invoice lines, currency id 1 is the target currency, and passing t.trandate uses the rate effective on each invoice date instead of today.

Gotchas

CURRENCY_CONVERT without a date uses today's rate, which silently restates history. CONSOLIDATE uses consolidated exchange rates per period, which is what financial statements use; pick it for anything that must tie to a consolidated report. BUILTIN.DF in GROUP BY must repeat the full expression. BUILTIN.PERIOD returns a set for IN, not a single value, while RELATIVE_RANGES returns one date. There is no LIMIT: use ROWNUM in a wrapping query or the paging features of N/query and the REST endpoint.

How to check the result in the system

Run the query for a small set of transactions and compare with the record. For DF, the value should match the field on the form. For CURRENCY_CONVERT, compare with the exchange rate on the Currency Exchange Rates page for that date. For CONSOLIDATE, compare with a consolidated financial report for the same period and subsidiary. Field names come from Setup > Records Catalog.

Frequently Asked Questions

What does BUILTIN.DF do in SuiteQL?

It returns the display value of a reference field, such as a customer name or status label, without joining the target table. BUILTIN.DF(t.entity) gives the customer name and BUILTIN.DF(t.status) gives Pending Fulfillment instead of B.

How many BUILTIN functions does SuiteQL support?

Oracle lists ten: CF, CONSOLIDATE, CURRENCY, CURRENCY_CONVERT, DF, HIERARCHY, MNFILTER, NAMED_GROUP, PERIOD and RELATIVE_RANGES. Each is called with the BUILTIN. prefix. Beyond these, SuiteQL accepts standard Oracle SQL functions such as NVL, CASE, TO_CHAR and TRUNC.

Does SuiteQL support LIMIT?

No. SuiteQL follows Oracle syntax, so use WHERE ROWNUM <= n, wrapping the query in a sub-select when you also sort. For larger results use runSuiteQLPaged in N/query or the limit and offset parameters of the REST suiteql endpoint.

What is the difference between CONSOLIDATE and CURRENCY_CONVERT?

CURRENCY_CONVERT uses the exchange rate table for a given date and target currency. CONSOLIDATE uses consolidated exchange rates (current, average or historical) for a target subsidiary and period, matching how NetSuite consolidates financial statements.

If you would rather have finance and operations ask these questions in plain English, we can set up a private AI layer over NetSuite that writes the SuiteQL and shows it.

Talk to an Expert Explore More Free Tools