What is SuiteQL?

SuiteQL is a query language based on the SQL-92 revision of the SQL database query language. It offers advanced query capabilities you can use to access your NetSuite records and data, providing more flexibility than traditional saved searches.

Key Characteristics

• SQL-92 Based: Familiar syntax for anyone with SQL experience
• Oracle SQL Support: Also supports Oracle SQL syntax (recommended for performance)
• Read-Only: SELECT queries only; no INSERT/UPDATE/DELETE operations
• Analytics Data Source: Queries the analytics data source, not the operational database

Access Methods

Prerequisites

To use SuiteQL, ensure these features are enabled:

• REST Web Services: Setup › Company › Enable Features › SuiteCloud › REST Web Services
• SuiteAnalytics Connect: For unlimited results (optional but recommended)
• Server SuiteScript: For N/query module access

Basic Query Structure

-- Basic SuiteQL Query Structure
SELECT
    column1,
    column2,
    BUILTIN.DF( foreignKeyColumn ) AS displayName
FROM
    tableName
WHERE
    condition1 = 'value'
    AND condition2 > 100
ORDER BY
    column1 DESC

Your First SuiteQL Query

-- Get all active customers
SELECT
    id,
    companyname,
    email,
    phone
FROM
    customer
WHERE
    isinactive = 'F'
ORDER BY
    companyname

Limiting Results

-- Limit to first 10 results using ROWNUM
SELECT * FROM customer WHERE RowNum <= 10

-- Using TOP (SQL-92 syntax)
SELECT TOP 10 * FROM customer

Primary Tables

NetSuite organizes data across several key tables. Understanding these relationships is essential for writing effective queries.

Transaction Types

All NetSuite transactions are stored in a single Transaction table, differentiated by the type field.

-- List all transaction types in your account
SELECT DISTINCT
    BUILTIN.DF( Type ) AS TypeName,
    Type AS TypeCode
FROM
    Transaction
ORDER BY
    BUILTIN.DF( Type )

Common Transaction Type Codes

Discovering Tables and Fields

Use the Records Catalog to explore available tables and fields:

Path: Setup › Records Catalog

Query Custom Record Types

-- List all custom record types
SELECT
    Name,
    ScriptID,
    InternalID,
    Description,
    BUILTIN.DF( Owner ) AS Owner
FROM
    CustomRecordType
ORDER BY
    Name

Query Custom Fields

-- List custom fields for a specific record type
SELECT
    Name,
    ScriptID,
    Description,
    FieldType,
    IsMandatory,
    IsStored
FROM
    CustomField
WHERE
    RecordType = 297  -- Use InternalID from CustomRecordType
ORDER BY
    Name

BUILTIN.DF (Display Field)

The most commonly used BUILTIN function. Returns the display value of a field from a related record without requiring an explicit JOIN. Think of "DF" as "Display Field" or "Display Format."

-- Without BUILTIN.DF - returns internal IDs
SELECT
    Transaction.Entity,        -- Returns: 12345
    Transaction.CreatedBy      -- Returns: 4
FROM Transaction
WHERE ID = 271017

-- With BUILTIN.DF - returns display values
SELECT
    BUILTIN.DF( Transaction.Entity ) AS CustomerName,     -- Returns: "Acme Corp"
    BUILTIN.DF( Transaction.CreatedBy ) AS CreatedByName  -- Returns: "John Smith"
FROM Transaction
WHERE ID = 271017

Common BUILTIN.DF Use Cases

SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS TransactionType,
    BUILTIN.DF( Transaction.Status ) AS Status,
    BUILTIN.DF( Transaction.Entity ) AS Customer,
    BUILTIN.DF( Transaction.Employee ) AS SalesRep,
    BUILTIN.DF( Transaction.Subsidiary ) AS Subsidiary,
    BUILTIN.DF( Transaction.Department ) AS Department,
    BUILTIN.DF( Transaction.PostingPeriod ) AS Period
FROM
    Transaction
WHERE
    Transaction.ID = 12345

BUILTIN.RELATIVE_RANGES

Returns dynamic calendar ranges for fiscal periods, weeks, months, and more. Eliminates hard-coded dates and automatically adapts to your account's fiscal calendar.

-- Get fiscal year boundaries
SELECT
    BUILTIN.RELATIVE_RANGES('TFY', 'START') AS ThisFiscalYearStart,
    BUILTIN.RELATIVE_RANGES('TFY', 'END') AS ThisFiscalYearEnd,
    BUILTIN.RELATIVE_RANGES('LFY', 'START') AS LastFiscalYearStart,
    BUILTIN.RELATIVE_RANGES('LFY', 'END') AS LastFiscalYearEnd
FROM DUAL

Common Range Codes

Using in WHERE Clause

-- Transactions from last fiscal year to date
SELECT
    COUNT(*) AS TransactionCount,
    SUM(ForeignTotal) AS TotalAmount
FROM
    Transaction
WHERE
    TranDate >= TO_DATE(BUILTIN.RELATIVE_RANGES('LFYTD', 'START'), 'MM/DD/YYYY')
    AND TranDate <= TO_DATE(BUILTIN.RELATIVE_RANGES('LFYTD', 'END'), 'MM/DD/YYYY')

Other BUILTIN Functions

Basic Transaction Query

-- Query transactions by date range
SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS Type,
    BUILTIN.DF( Transaction.Status ) AS Status,
    BUILTIN.DF( Transaction.Entity ) AS Entity,
    Transaction.ForeignTotal AS Amount
FROM
    Transaction
WHERE
    Transaction.TranDate BETWEEN
        TO_DATE('2025-01-01', 'YYYY-MM-DD')
        AND TO_DATE('2025-12-31', 'YYYY-MM-DD')
    AND Transaction.Type = 'CustInvc'
ORDER BY
    Transaction.TranDate DESC

Transaction with Entity Details

-- Join Transaction to Entity and Employee
SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS Type,
    BUILTIN.DF( Transaction.Status ) AS Status,
    BUILTIN.DF( Transaction.Entity ) AS Customer,
    Entity.Email AS CustomerEmail,
    Entity.Phone AS CustomerPhone,
    BUILTIN.DF( Transaction.Employee ) AS SalesRep,
    Employee.Email AS SalesRepEmail
FROM
    Transaction
    INNER JOIN Entity ON Entity.ID = Transaction.Entity
    LEFT OUTER JOIN Employee ON Employee.ID = Transaction.Employee
WHERE
    Transaction.TranDate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
    AND Transaction.Type = 'SalesOrd'

Transaction with Line Items

-- Query transactions with line item details
SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS Type,
    BUILTIN.DF( Transaction.Entity ) AS Customer,
    BUILTIN.DF( TransactionLine.Item ) AS Item,
    TransactionLine.Quantity,
    TransactionLine.Rate,
    TransactionLine.Amount,
    BUILTIN.DF( TransactionLine.Department ) AS Department,
    BUILTIN.DF( TransactionLine.Class ) AS Class
FROM
    Transaction
    INNER JOIN TransactionLine ON TransactionLine.Transaction = Transaction.ID
WHERE
    Transaction.Type = 'SalesOrd'
    AND Transaction.TranDate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
    AND TransactionLine.MainLine = 'F'  -- Exclude header/mainline
ORDER BY
    Transaction.TranID,
    TransactionLine.LineSequenceNumber

Full Transaction Query with Items

-- Complete transaction query with item details
SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS TransactionType,
    BUILTIN.DF( Transaction.Status ) AS Status,
    BUILTIN.DF( Transaction.Entity ) AS Customer,
    BUILTIN.DF( TransactionLine.Item ) AS ItemName,
    Item.ItemID AS ItemCode,
    BUILTIN.DF( Item.ItemType ) AS ItemType,
    TransactionLine.Quantity,
    TransactionLine.Rate,
    TransactionLine.Amount
FROM
    Transaction
    INNER JOIN Entity ON Entity.ID = Transaction.Entity
    INNER JOIN TransactionLine ON TransactionLine.Transaction = Transaction.ID
    INNER JOIN Item ON Item.ID = TransactionLine.Item
WHERE
    Transaction.TranDate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
    AND TransactionLine.MainLine = 'F'
ORDER BY
    Transaction.TranDate DESC,
    Transaction.TranID

Customer Transaction Ledger

-- Customer transaction ledger (like Sales > Transactions sublist)
SELECT
    Transaction.TranID AS DocumentNumber,
    Transaction.TranDate AS Date,
    BUILTIN.DF( Transaction.Type ) AS Type,
    BUILTIN.DF( Transaction.Status ) AS Status,
    Transaction.ForeignTotal AS Amount,
    Transaction.ForeignAmountRemaining AS AmountDue
FROM
    Transaction
WHERE
    Transaction.Entity = 12345  -- Customer internal ID
    AND Transaction.Type IN ('CustInvc', 'CustCred', 'CustPymt', 'SalesOrd')
ORDER BY
    Transaction.TranDate DESC

Understanding TransactionAccountingLine

The TransactionAccountingLine table contains the GL impact of transactions—the same data you see on the "GL Impact" tab of a transaction. This table is essential for financial reporting and audit trails.

Basic GL Impact Query

-- Get GL impact for a specific transaction
SELECT
    Transaction.TranID,
    BUILTIN.DF( TransactionAccountingLine.AccountingBook ) AS Book,
    BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
    TransactionAccountingLine.Debit,
    TransactionAccountingLine.Credit,
    TransactionAccountingLine.Posting,
    BUILTIN.DF( Transaction.Entity ) AS EntityName,
    TransactionLine.Memo,
    BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
    BUILTIN.DF( TransactionLine.Department ) AS Department
FROM
    Transaction
    INNER JOIN TransactionAccountingLine ON
        TransactionAccountingLine.Transaction = Transaction.ID
    LEFT OUTER JOIN TransactionLine ON
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
        AND TransactionLine.ID = TransactionAccountingLine.TransactionLine
WHERE
    Transaction.ID = 26254
    AND TransactionAccountingLine.Account IS NOT NULL
ORDER BY
    TransactionAccountingLine.TransactionLine

GL Impact by Transaction Type

-- Analyze GL impact by transaction type
SELECT
    BUILTIN.DF( Transaction.Type ) AS TransactionType,
    BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
    SUM( TransactionAccountingLine.Debit ) AS TotalDebits,
    SUM( TransactionAccountingLine.Credit ) AS TotalCredits
FROM
    Transaction
    INNER JOIN TransactionAccountingLine ON
        TransactionAccountingLine.Transaction = Transaction.ID
WHERE
    Transaction.TranDate BETWEEN
        TO_DATE('2025-01-01', 'YYYY-MM-DD')
        AND TO_DATE('2025-01-31', 'YYYY-MM-DD')
    AND TransactionAccountingLine.Posting = 'T'
GROUP BY
    BUILTIN.DF( Transaction.Type ),
    BUILTIN.DF( TransactionAccountingLine.Account )
ORDER BY
    BUILTIN.DF( Transaction.Type ),
    BUILTIN.DF( TransactionAccountingLine.Account )

Transactions Posted to Specific Account

-- Find all transactions affecting a specific GL account
SELECT
    Transaction.TranID,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.Type ) AS Type,
    BUILTIN.DF( Transaction.Entity ) AS Entity,
    TransactionAccountingLine.Debit,
    TransactionAccountingLine.Credit,
    TransactionLine.Memo
FROM
    Transaction
    INNER JOIN TransactionAccountingLine ON
        TransactionAccountingLine.Transaction = Transaction.ID
    LEFT OUTER JOIN TransactionLine ON
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
        AND TransactionLine.ID = TransactionAccountingLine.TransactionLine
WHERE
    TransactionAccountingLine.Account = 123  -- Account internal ID
    AND TransactionAccountingLine.Posting = 'T'
    AND Transaction.TranDate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
ORDER BY
    Transaction.TranDate DESC

Journal Entry Details

-- Query journal entry details
SELECT
    Transaction.TranID AS JournalNumber,
    Transaction.TranDate,
    BUILTIN.DF( Transaction.PostingPeriod ) AS Period,
    BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
    TransactionAccountingLine.Debit,
    TransactionAccountingLine.Credit,
    TransactionLine.Memo AS LineMemo,
    BUILTIN.DF( TransactionLine.Entity ) AS Name,
    BUILTIN.DF( TransactionLine.Department ) AS Department,
    BUILTIN.DF( TransactionLine.Class ) AS Class,
    BUILTIN.DF( TransactionLine.Location ) AS Location
FROM
    Transaction
    INNER JOIN TransactionAccountingLine ON
        TransactionAccountingLine.Transaction = Transaction.ID
    LEFT OUTER JOIN TransactionLine ON
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
        AND TransactionLine.ID = TransactionAccountingLine.TransactionLine
WHERE
    Transaction.Type = 'Journal'
    AND Transaction.TranDate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
ORDER BY
    Transaction.TranDate,
    Transaction.TranID

Current Date/Time Functions

-- Get current date/time
SELECT
    SYSDATE AS ServerDateTime,        -- Server time
    CURRENT_DATE AS UserDateTime,     -- User session time
    TRUNC(SYSDATE) AS TodayOnly,      -- Date portion only
    SESSIONTIMEZONE AS UserTimeZone   -- User's time zone
FROM DUAL

TO_DATE and TO_CHAR

-- Convert string to date
SELECT *
FROM Transaction
WHERE TranDate > TO_DATE('2025-01-01', 'YYYY-MM-DD')

-- Various date formats for TO_DATE
TO_DATE('11/16/2025', 'MM/DD/YYYY')
TO_DATE('16-11-2025', 'DD-MM-YYYY')
TO_DATE('Nov 16, 2025', 'MON DD, YYYY')
TO_DATE('November 16 2025', 'MONTH DD YYYY')

-- Format date as string using TO_CHAR
SELECT
    TranID,
    TO_CHAR(TranDate, 'YYYY-MM-DD') AS FormattedDate,
    TO_CHAR(TranDate, 'Month DD, YYYY') AS LongDate,
    TO_CHAR(TranDate, 'DS TS') AS ShortDateTime
FROM Transaction

Common Format Elements

Date Arithmetic

-- Add/subtract days
SELECT
    SYSDATE AS Today,
    SYSDATE - 1 AS Yesterday,
    SYSDATE + 7 AS NextWeek,
    SYSDATE - 30 AS ThirtyDaysAgo
FROM DUAL

-- Calculate age in days
SELECT
    TranID,
    TranDate,
    TRUNC(SYSDATE) - TranDate AS DaysOld
FROM Transaction
WHERE Type = 'CustInvc'
ORDER BY DaysOld DESC

-- Add/subtract months
SELECT
    SYSDATE AS Today,
    ADD_MONTHS(SYSDATE, 1) AS NextMonth,
    ADD_MONTHS(SYSDATE, -3) AS ThreeMonthsAgo,
    ADD_MONTHS(SYSDATE, 12) AS NextYear
FROM DUAL

-- Get last day of month
SELECT
    LAST_DAY(SYSDATE) AS EndOfMonth,
    LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS EndOfNextMonth
FROM DUAL

-- Get next specific weekday
SELECT
    NEXT_DAY(SYSDATE, 'MONDAY') AS NextMonday,
    NEXT_DAY(SYSDATE, 'FRIDAY') AS NextFriday
FROM DUAL

Time Arithmetic

-- Add/subtract hours, minutes, seconds
SELECT
    SYSDATE AS Now,
    SYSDATE - 1/24 AS OneHourAgo,           -- 1 hour = 1/24 of a day
    SYSDATE + 30/1440 AS In30Minutes,       -- 30 mins = 30/1440 of a day
    SYSDATE - 3600/86400 AS OneHourAgoAlt   -- 3600 secs = 3600/86400
FROM DUAL

Time Zone Functions

-- Get time zone offset
SELECT
    TZ_OFFSET('US/Eastern') AS EasternOffset,
    TZ_OFFSET('US/Pacific') AS PacificOffset,
    TZ_OFFSET(SESSIONTIMEZONE) AS UserOffset,
    SESSIONTIMEZONE AS UserTimeZone
FROM DUAL

Common String Functions

-- Case conversion
SELECT
    UPPER(companyname) AS UpperCase,
    LOWER(email) AS LowerCase,
    INITCAP(companyname) AS TitleCase
FROM Customer

-- String concatenation
SELECT
    CONCAT(firstname, lastname) AS FullName,
    firstname || ' ' || lastname AS FullNameAlt  -- Preferred syntax
FROM Employee

-- Substring extraction
SELECT
    SUBSTR(phone, 1, 3) AS AreaCode,        -- First 3 characters
    SUBSTR(entityid, 5) AS AfterPrefix      -- From position 5 to end
FROM Customer

-- String length
SELECT
    companyname,
    LENGTH(companyname) AS NameLength
FROM Customer

-- Trim whitespace
SELECT
    TRIM(companyname) AS Trimmed,
    LTRIM(companyname) AS LeftTrimmed,
    RTRIM(companyname) AS RightTrimmed
FROM Customer

NVL and Null Handling

-- Replace null with default value
SELECT
    companyname,
    NVL(phone, 'No Phone') AS Phone,
    NVL(email, 'No Email') AS Email,
    NVL(fax, 'N/A') AS Fax
FROM Customer

-- NVL2: Different values for null vs non-null
SELECT
    companyname,
    NVL2(email, 'Has Email', 'No Email') AS EmailStatus
FROM Customer

-- COALESCE: Return first non-null value
SELECT
    COALESCE(workphone, homephone, mobilephone, 'No Phone') AS BestPhone
FROM Contact

Pattern Matching

-- LIKE pattern matching
SELECT * FROM Customer
WHERE companyname LIKE 'Acme%'      -- Starts with "Acme"

SELECT * FROM Customer
WHERE email LIKE '%@gmail.com'      -- Gmail addresses

SELECT * FROM Customer
WHERE phone LIKE '___-___-____'     -- Phone pattern

-- INSTR: Find position of substring
SELECT
    companyname,
    INSTR(companyname, 'Inc') AS IncPosition
FROM Customer
WHERE INSTR(companyname, 'Inc') > 0

-- REPLACE: Replace substring
SELECT
    phone,
    REPLACE(phone, '-', '.') AS DottedPhone,
    REPLACE(REPLACE(phone, '-', ''), ' ', '') AS DigitsOnly
FROM Customer

Regular Expressions

-- REGEXP_SUBSTR: Extract using regex
SELECT
    email,
    REGEXP_SUBSTR(email, '[^@]+') AS Username,
    REGEXP_SUBSTR(email, '@(.+)', 1, 1, NULL, 1) AS Domain
FROM Customer

-- Pattern matching with REGEXP_LIKE
SELECT *
FROM Customer
WHERE REGEXP_LIKE(phone, '^\d{3}-\d{3}-\d{4}$')

Basic Usage

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/query'], function(query) {

    function onRequest(context) {
        // Simple query
        var sql = `
            SELECT id, companyname, email
            FROM customer
            WHERE isinactive = 'F'
            ORDER BY companyname
        `;

        var results = query.runSuiteQL({
            query: sql
        });

        // Get results as array of objects
        var customers = results.asMappedResults();

        // Process results
        customers.forEach(function(customer) {
            log.debug('Customer', customer.companyname + ': ' + customer.email);
        });
    }

    return { onRequest: onRequest };
});

Governance Units

Parameterized Queries

// Using parameters for safe queries (prevents SQL injection)
var sql = `
    SELECT id, companyname, email
    FROM customer
    WHERE subsidiary = ?
    AND creditlimit > ?
`;

var results = query.runSuiteQL({
    query: sql,
    params: [1, 10000]  // Parameters in order
});

var customers = results.asMappedResults();

Paged Queries

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query'], function(query) {

    function execute(context) {
        var sql = `
            SELECT id, tranid, trandate, foreigntotal
            FROM transaction
            WHERE type = 'CustInvc'
            ORDER BY trandate DESC, id
        `;

        // Run as paged query
        var pagedResults = query.runSuiteQLPaged({
            query: sql,
            pageSize: 1000  // 5 to 1000, default 50
        });

        log.debug('Total Results', pagedResults.count);

        // Iterate through pages
        pagedResults.iterator().each(function(page) {
            var pageData = page.value.data;

            pageData.iterator().each(function(row) {
                var tranId = row.value.getValue(0);
                var tranDate = row.value.getValue(1);
                // Process each row...
                return true;  // Continue iteration
            });

            return true;  // Continue to next page
        });
    }

    return { execute: execute };
});

Result Processing Methods

var results = query.runSuiteQL({ query: sql });

// Method 1: asMappedResults() - Array of objects
var mapped = results.asMappedResults();
// Returns: [{id: 1, name: 'Acme'}, {id: 2, name: 'Beta'}]

// Method 2: Iterator with getValue()
results.iterator().each(function(result) {
    var id = result.value.getValue(0);      // By column index
    var name = result.value.getValue('companyname');  // By column name
    return true;
});

// Method 3: results.results array
var allResults = results.results;
allResults.forEach(function(row) {
    var values = row.values;
    // values is array of column values
});

Convert Saved Search to SuiteQL

// Load a saved search or workbook and convert to SuiteQL
var myQuery = query.load({
    id: 'custworkbook237'
});

// Convert to SuiteQL object
var suiteQL = myQuery.toSuiteQL();

// Run the converted query
var results = suiteQL.run();
var data = results.asMappedResults();

REST Endpoint

POST https://{accountId}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql

Headers:
  Content-Type: application/json
  Authorization: OAuth 1.0 or OAuth 2.0
  Prefer: transient

Body:
{
  "q": "SELECT id, companyname, email FROM customer WHERE isinactive = 'F' ORDER BY companyname"
}

Response Structure

{
  "links": [...],
  "count": 150,
  "hasMore": true,
  "offset": 0,
  "totalResults": 1523,
  "items": [
    {"id": "123", "companyname": "Acme Corp", "email": "info@acme.com"},
    {"id": "124", "companyname": "Beta Inc", "email": "contact@beta.com"}
  ]
}

Pagination via REST

-- URL parameters for pagination
POST /services/rest/query/v1/suiteql?limit=1000&offset=0

-- Get next page
POST /services/rest/query/v1/suiteql?limit=1000&offset=1000

-- Get third page
POST /services/rest/query/v1/suiteql?limit=1000&offset=2000

Limits

cURL Example

curl -X POST \
  'https://123456.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=100' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: OAuth oauth_consumer_key="...", oauth_token="...", ...' \
  -H 'Prefer: transient' \
  -d '{
    "q": "SELECT id, companyname FROM customer WHERE isinactive = '\''F'\'' ORDER BY id"
  }'

Python Example

import requests
from requests_oauthlib import OAuth1

# OAuth 1.0 credentials
auth = OAuth1(
    client_key='consumer_key',
    client_secret='consumer_secret',
    resource_owner_key='token_key',
    resource_owner_secret='token_secret',
    realm='123456'  # Account ID
)

url = 'https://123456.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

query = """
    SELECT id, companyname, email
    FROM customer
    WHERE isinactive = 'F'
    ORDER BY companyname
"""

response = requests.post(
    url,
    auth=auth,
    headers={
        'Content-Type': 'application/json',
        'Prefer': 'transient'
    },
    json={'q': query},
    params={'limit': 1000}
)

data = response.json()
customers = data['items']

# Paginate if more results
while data.get('hasMore'):
    offset = data['offset'] + data['count']
    response = requests.post(
        url,
        auth=auth,
        headers={'Content-Type': 'application/json', 'Prefer': 'transient'},
        json={'q': query},
        params={'limit': 1000, 'offset': offset}
    )
    data = response.json()
    customers.extend(data['items'])

Understanding ROWNUM

ROWNUM is a pseudocolumn that indicates the position of a row in a result set. It behaves like a column but isn't stored in the table. Crucially, ROWNUM is assigned after filtering but before sorting, which can cause unexpected behavior.

-- Problem: ROWNUM assigned before ORDER BY
SELECT * FROM Transaction WHERE RowNum <= 10 ORDER BY TranDate DESC
-- Returns: 10 arbitrary rows, THEN sorts them (wrong!)

-- Solution: Nested subquery
SELECT * FROM (
    SELECT * FROM Transaction ORDER BY TranDate DESC
) WHERE RowNum <= 10
-- Returns: First 10 rows by date (correct!)

Pagination with ROWNUM

-- Get rows 11-20 (page 2 with 10 per page)
SELECT * FROM (
    SELECT ROWNUM AS RN, inner_query.* FROM (
        SELECT
            id,
            tranid,
            trandate,
            foreigntotal
        FROM Transaction
        WHERE Type = 'CustInvc'
        ORDER BY TranDate DESC, ID
    ) inner_query
)
WHERE RN BETWEEN 11 AND 20

Pagination Pattern Explained

1. Innermost query: Your actual query with ORDER BY
2. Middle query: Adds ROWNUM as RN column to sorted results
3. Outer query: Filters by row number range

Alternative: FETCH NEXT

-- SQL standard pagination (where supported)
SELECT id, companyname, email
FROM customer
ORDER BY companyname
OFFSET 100 ROWS
FETCH NEXT 50 ROWS ONLY

Query Optimization

1. Use Oracle SQL Syntax

-- Prefer Oracle syntax over SQL-92
-- Oracle recommends this to avoid performance issues and timeouts

2. Select Only Needed Columns

-- Bad: Select all columns
SELECT * FROM Transaction WHERE Type = 'CustInvc'

-- Good: Select specific columns
SELECT id, tranid, trandate, foreigntotal
FROM Transaction
WHERE Type = 'CustInvc'

3. Use BUILTIN.DF Instead of JOINs

-- Slower: Explicit JOIN
SELECT t.tranid, c.companyname
FROM Transaction t
INNER JOIN Customer c ON c.id = t.entity
WHERE t.type = 'CustInvc'

-- Faster: BUILTIN.DF (often)
SELECT tranid, BUILTIN.DF(entity) AS CustomerName
FROM Transaction
WHERE type = 'CustInvc'

4. Filter Early

-- Filter in WHERE, not after aggregation when possible
SELECT BUILTIN.DF(subsidiary), SUM(foreigntotal)
FROM Transaction
WHERE type = 'CustInvc'
  AND trandate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
GROUP BY BUILTIN.DF(subsidiary)

5. Use Indexed Fields for Filtering

Filter on fields that are commonly indexed:

• id (primary key)
• type (transaction type)
• trandate
• entity
• subsidiary

Common Mistakes to Avoid

Query Limits Reference

Common Errors

Invalid Column Name

Error: Invalid column name 'customfield'

Cause: Field doesn't exist or has different internal name
Solution: Check Records Catalog for exact field name
         Custom fields use scriptid (e.g., 'custbody_myfield')

Field Case Sensitivity

-- Field names may appear differently in results
-- Account vs ACCOUNT vs account

Solution: Don't depend on case in your code
         Convert to consistent case: UPPER() or LOWER()

Syntax Errors from Mixed Dialects

Error: Unexpected token

Cause: Mixing SQL-92 and Oracle SQL syntax
Solution: Use only one syntax style per query

Permission Errors

Error: You do not have access to this record type

Cause: Role lacks permission to query the table
Solution: Check role permissions for:
         - Record type access
         - Field-level access
         - Subsidiary restrictions

Known Limitations

Advanced Workarounds

Cumulative Totals (No Window Functions)

-- SuiteQL lacks SUM() OVER() for running totals
-- Workaround: Self-join approach

SELECT
    a.trandate,
    a.foreigntotal AS DailyTotal,
    (
        SELECT SUM(b.foreigntotal)
        FROM Transaction b
        WHERE b.type = 'CustInvc'
          AND b.trandate <= a.trandate
    ) AS RunningTotal
FROM Transaction a
WHERE a.type = 'CustInvc'
ORDER BY a.trandate

Debugging Tips

Comment Out Sections

SELECT
    Transaction.ID,
    Transaction.TranID
    /* Customer.LastName,
       Customer.FirstName */
FROM Transaction
-- INNER JOIN Customer ON Customer.ID = Transaction.Entity
WHERE Transaction.Type = 'SalesOrd'
AND RowNum <= 10

Start Simple, Add Complexity

1. Write minimal query that returns any data
2. Add one JOIN at a time
3. Add filters incrementally
4. Add GROUP BY and aggregations last

SuiteQL Query Tool

The SuiteQL Query Tool by Tim Dietrich is a free, NetSuite-native Suitelet that allows you to run SQL queries directly within your NetSuite instance.

Features

• Run SuiteQL queries directly in NetSuite UI
• Save queries to File Cabinet
• Load queries from shared library
• Export results to CSV
• SuiteQL Tables Reference included
• Syntax highlighting and formatting

Download: timdietrich.me/netsuite-suitescripts/suiteql-query-tool/

Official Documentation

Community Resources

Tim Dietrich (SuiteStep)

Developer of the SuiteQL Query Tool and prolific blogger on NetSuite topics.

• Blog: timdietrich.me/blog/
• SuiteQL Topics: Transaction tables, GL impact, date functions, custom records

Prolecto Resources

NetSuite systems integrator with advanced SuiteQL tutorials and utilities.

• Blog: blog.prolecto.com/
• Topics: ABC analysis, system notes, content rendering

NetSuite Professionals Community

Active Slack community for NetSuite developers and consultants.

• Website: netsuiteprofessionals.com/
• #suiteql channel: Active Q&A and discussions

NetSuite Training

• SuiteQL Essentials Course: Official NetSuite Training

SuiteAnswers References

Access SuiteAnswers at: Setup › Support › SuiteAnswers

Quick Reference Card

-- QUICK REFERENCE: Common SuiteQL Patterns
===============================================================

-- Display value of foreign key
BUILTIN.DF( fieldname ) AS DisplayName

-- Date filtering
WHERE trandate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')

-- Dynamic fiscal year
WHERE trandate >= TO_DATE(BUILTIN.RELATIVE_RANGES('TFY', 'START'), 'MM/DD/YYYY')

-- Limit results
WHERE RowNum <= 100

-- Exclude mainline in transaction lines
WHERE TransactionLine.MainLine = 'F'

-- Only posted transactions
WHERE TransactionAccountingLine.Posting = 'T'

-- Null handling
NVL(fieldname, 'default')

-- Date arithmetic
SYSDATE - 30                    -- 30 days ago
ADD_MONTHS(SYSDATE, -1)         -- 1 month ago
TRUNC(SYSDATE) - TranDate       -- Days old

-- Key Tables:
-- Transaction, TransactionLine, TransactionAccountingLine
-- Entity, Customer, Vendor, Employee
-- Item, Account, Subsidiary, Department, Class, Location