# NetSuite SuiteQL BUILTIN Functions Reference > Source: https://netray.co/tools/netsuite-suiteql-builtin-functions-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer The ten BUILTIN functions Oracle documents for SuiteQL, with exact parameter order and one-line examples, plus the Oracle SQL functions you will use beside them. ## Category netsuite ## Erp NetSuite ## Audience - NetSuite developer - BI and reporting analyst - Integration engineer ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Group **Key:** group **Type:** text #### Function **Key:** function **Type:** text #### Purpose **Key:** purpose **Type:** text #### Signature (parameters in order) **Key:** signature **Type:** text #### Example **Key:** example **Type:** text #### Returns **Key:** returns **Type:** text #### Notes **Key:** notes **Type:** text **Rows:** **Group:** BUILTIN **Function:** BUILTIN.CF **Purpose:** Criteria value of a field with combined values, such as transaction status **Signature:** BUILTIN.CF(field) **Example:** WHERE BUILTIN.CF(t.status) = 'SalesOrd:B' **Returns:** Type and status combined, for example 'CustInvc:B' **Notes:** Oracle says to use it in filters on composite key fields to avoid wrong results. **Group:** BUILTIN **Function:** BUILTIN.CONSOLIDATE **Purpose:** Convert an amount to a target subsidiary currency using consolidation rates **Signature:** BUILTIN.CONSOLIDATE(amount_field, view_type, consolidation_rate_type, subsidiary_rate_type, target_subsidiary, period, book) **Example:** BUILTIN.CONSOLIDATE(tal.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, t.postingperiod, 'DEFAULT') **Returns:** Converted amount **Notes:** 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. **Group:** BUILTIN **Function:** BUILTIN.CURRENCY **Purpose:** Return the currency code of an amount **Signature:** BUILTIN.CURRENCY(amount) **Example:** BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(tal.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 1, t.postingperiod, 'DEFAULT')) **Returns:** Currency code **Notes:** Accepts amount, SUM(amount), BUILTIN.CONSOLIDATE or BUILTIN.CURRENCY_CONVERT and their SUM. **Group:** BUILTIN **Function:** BUILTIN.CURRENCY_CONVERT **Purpose:** Convert an amount to a target currency using the exchange rate effective on a date **Signature:** BUILTIN.CURRENCY_CONVERT(amount_field [, target_currency_id [, rate_date]]) **Example:** BUILTIN.CURRENCY_CONVERT(tl.foreignamount, 1, TO_DATE('2026-09-30', 'YYYY-MM-DD')) **Returns:** Converted amount **Notes:** Target defaults to the subsidiary currency; rate date defaults to today, so pass the transaction date for historic reports. **Group:** BUILTIN **Function:** BUILTIN.DF **Purpose:** Display value of a reference field without joining the target table **Signature:** BUILTIN.DF(field) **Example:** SELECT BUILTIN.DF(t.status), BUILTIN.DF(tl.item) FROM transaction t JOIN transactionline tl ON tl.transaction = t.id **Returns:** Display text **Notes:** The function you will use most. Works on list, record and status fields, including custom ones. **Group:** BUILTIN **Function:** BUILTIN.HIERARCHY **Purpose:** Full hierarchical path of a value such as a parent item, department or account **Signature:** BUILTIN.HIERARCHY(hierarchical_field, expansion_type) **Example:** SELECT id, parent, BUILTIN.HIERARCHY(parent, 'DISPLAY_JOINED') FROM item **Returns:** Path as ids, names or level depending on expansion type **Notes:** '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. **Group:** BUILTIN **Function:** BUILTIN.MNFILTER **Purpose:** Filter on a multiple-select field **Signature:** BUILTIN.MNFILTER(relationship_field, operator, display_field_or_empty, case_sensitive, value) **Example:** WHERE BUILTIN.MNFILTER(role.accessprofiles, 'MN_INCLUDE', '', 'TRUE', '-2') = 'T' **Returns:** Used as a T/F test in WHERE **Notes:** 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. **Group:** BUILTIN **Function:** BUILTIN.NAMED_GROUP **Purpose:** Records that belong to the current user: me or mine **Signature:** BUILTIN.NAMED_GROUP(record_type, workgroup) **Example:** SELECT * FROM subsidiary WHERE id IN BUILTIN.NAMED_GROUP('subsidiary', 'mine') **Returns:** A set of ids for an IN clause **Notes:** 'me' for entity types (employee, customer, vendor, partner); 'mine' for classification, department, location and subsidiary. **Group:** BUILTIN **Function:** BUILTIN.PERIOD **Purpose:** Accounting periods for a relative period range, as an IN sub-select **Signature:** BUILTIN.PERIOD(range_id, range_type, adjustment, operator) **Example:** WHERE t.postingperiod IN BUILTIN.PERIOD('LFY', 'START', 'ALL', 'BETWEEN') **Returns:** Contents of an IN predicate (period ids) **Notes:** range_type 'START' or 'END'; adjustment 'NOT_LAST' or 'ALL'; operator 'BETWEEN', 'NOT BETWEEN', '<', '<=', '>', '>='. Range IDs come from the query.RelativeDateRange list. **Group:** BUILTIN **Function:** BUILTIN.RELATIVE_RANGES **Purpose:** Start or end date of a relative date range such as last fiscal year **Signature:** BUILTIN.RELATIVE_RANGES(range_id, range_type [, date_flag]) **Example:** WHERE t.trandate >= BUILTIN.RELATIVE_RANGES('LFY', 'START') AND t.trandate <= BUILTIN.RELATIVE_RANGES('LFY', 'END') **Returns:** A date (or timestamp, depending on date_flag) **Notes:** 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. **Group:** Oracle SQL functions supported **Function:** NVL **Purpose:** Replace NULL with a default **Signature:** NVL(expr, default) **Example:** NVL(tl.quantityshiprecv, 0) **Returns:** expr or default **Notes:** Use before arithmetic; NULL plus a number is NULL. **Group:** Oracle SQL functions supported **Function:** NVL2 **Purpose:** One value when not NULL, another when NULL **Signature:** NVL2(expr, if_not_null, if_null) **Example:** NVL2(t.duedate, 'Has terms', 'No due date') **Returns:** Either branch **Notes:** Both branches must be the same data type. **Group:** Oracle SQL functions supported **Function:** COALESCE **Purpose:** First non-NULL of several values **Signature:** COALESCE(expr1, expr2, ...) **Example:** COALESCE(tl.memo, t.memo, '') **Returns:** First non-NULL value **Notes:** Cleaner than nested NVL. **Group:** Oracle SQL functions supported **Function:** CASE **Purpose:** Conditional logic **Signature:** CASE WHEN cond THEN a [WHEN ...] ELSE b END **Example:** CASE WHEN t.status = 'H' THEN 'Closed' ELSE 'Open' END **Returns:** Branch value **Notes:** Prefer it over DECODE for readability. **Group:** Oracle SQL functions supported **Function:** DECODE **Purpose:** Map values in a compact form **Signature:** DECODE(expr, search1, result1, ..., default) **Example:** DECODE(t.type, 'SalesOrd', 'Order', 'CustInvc', 'Invoice', 'Other') **Returns:** Mapped value **Notes:** Oracle-specific; treats two NULLs as equal. **Group:** Oracle SQL functions supported **Function:** TO_CHAR **Purpose:** Format a date or number as text **Signature:** TO_CHAR(value, format) **Example:** TO_CHAR(t.trandate, 'YYYY-MM') **Returns:** Text **Notes:** Good for month buckets in GROUP BY. **Group:** Oracle SQL functions supported **Function:** TO_DATE **Purpose:** Text to date **Signature:** TO_DATE(text, format) **Example:** t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD') **Returns:** Date **Notes:** Always pass the format; account date preferences differ. **Group:** Oracle SQL functions supported **Function:** TRUNC **Purpose:** Cut a date to the day, month or year (or a number to n decimals) **Signature:** TRUNC(date [, unit]) or TRUNC(number [, n]) **Example:** TRUNC(t.createddate) **Returns:** Date or number **Notes:** Use on timestamp columns before comparing with dates. **Group:** Oracle SQL functions supported **Function:** ADD_MONTHS **Purpose:** Date plus or minus months **Signature:** ADD_MONTHS(date, n) **Example:** t.trandate >= ADD_MONTHS(TRUNC(SYSDATE), -12) **Returns:** Date **Notes:** Negative n goes back in time. **Group:** Oracle SQL functions supported **Function:** SYSDATE **Purpose:** Current date and time **Signature:** SYSDATE **Example:** TRUNC(SYSDATE) - t.trandate **Returns:** Date **Notes:** Subtracting dates gives days as a number. **Group:** Oracle SQL functions supported **Function:** ROUND **Purpose:** Round a number **Signature:** ROUND(number [, n]) **Example:** ROUND(tl.rate * ABS(tl.quantity), 2) **Returns:** Number **Notes:** Also rounds dates when given a date. **Group:** Oracle SQL functions supported **Function:** SUBSTR **Purpose:** Part of a string **Signature:** SUBSTR(text, start [, length]) **Example:** SUBSTR(i.itemid, 1, 3) **Returns:** Text **Notes:** Positions start at 1. **Group:** Oracle SQL functions supported **Function:** INSTR **Purpose:** Position of a substring **Signature:** INSTR(text, search [, start [, occurrence]]) **Example:** INSTR(i.itemid, '-') **Returns:** Number, 0 if not found **Notes:** Combine with SUBSTR to split part number prefixes. **Group:** Oracle SQL functions supported **Function:** ROWNUM **Purpose:** Limit rows (SuiteQL has no LIMIT keyword) **Signature:** WHERE ROWNUM <= n **Example:** SELECT * FROM (SELECT id, tranid FROM transaction ORDER BY id DESC) WHERE ROWNUM <= 50 **Returns:** Row number pseudo column **Notes:** 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. **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use it **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### What does BUILTIN.DF do in SuiteQL? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms netsuite suiteql built in functions, netsuite suiteql df, netsuite suiteql reference, suiteql in netsuite, builtin.df suiteql, builtin.consolidate suiteql, builtin.currency_convert, suiteql rownum --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json