Introduction to SuiteQL

SuiteQL is NetSuite's SQL-like query language that provides powerful, flexible data retrieval capabilities using familiar SQL syntax.

---

What is SuiteQL?

SuiteQL allows you to query NetSuite data using standard SQL syntax instead of the proprietary N/search API. It provides:

• Familiar SQL syntax - SELECT, FROM, WHERE, JOIN, GROUP BY
• Better performance - Optimized for complex queries
• More flexibility - Advanced operations not available in N/search
• Analytics tables - Access to pre-joined, flattened data views

---

Where Can You Use SuiteQL?

Method	Use Case
N/query module	SuiteScript automation, Suitelets, scheduled scripts
REST API	External integrations, third-party applications
Analytics Workbook	Interactive reporting, dashboards

---

SuiteQL vs N/search: When to Use Each

Quick Comparison

Feature	SuiteQL	N/search
Syntax	SQL (familiar)	NetSuite-specific API
Joins	Explicit SQL JOINs	Automatic via relationships
Complex queries	Excellent	Limited
Window functions	Yes	No
CTEs (WITH clause)	Yes	No
UNION operations	Yes	No
Learning curve	Easy (if you know SQL)	Steeper
UI Builder	Limited	Saved Search UI

When to Use SuiteQL

Use SuiteQL When:

• You need complex multi-table JOINs
• You want window functions (ROW_NUMBER, RANK, etc.)
• You need UNION to combine result sets
• You're comfortable with SQL
• You need hierarchical queries (parent-child)
• You want CTEs for readable complex queries

Use N/search When:

• Simple record lookups
• You need the Saved Search UI
• Formula fields with NetSuite-specific functions
• You want automatic join handling
• You need to save and share searches with users

---

Basic Syntax Overview

Simple Query Structure

SELECT column1, column2, column3
FROM table_name
WHERE condition
ORDER BY column1
LIMIT 100

Real NetSuite Example

-- Get active customers with their balance
SELECT
    id,
    companyname,
    email,
    balance
FROM
    customer
WHERE
    isinactive = 'F'
    AND balance > 0
ORDER BY
    balance DESC
LIMIT 50

Understanding Table Names

NetSuite tables use record type names in lowercase:

Record Type	Table Name
Customer	customer
Sales Order	transaction (with type filter)
Invoice	transaction (with type filter)
Item	item
Employee	employee
Vendor	vendor
Custom Record	customrecord_yourrecordid

---

Your First SuiteQL Query

Step 1: Simple Customer List

-- Most basic query: list all customers
SELECT id, companyname
FROM customer
LIMIT 10

Result:

id	companyname
1	Acme Corporation
2	Global Industries
3	Tech Solutions

Step 2: Add Filtering

-- Active customers only
SELECT id, companyname, email
FROM customer
WHERE isinactive = 'F'
LIMIT 10

Step 3: Add Sorting

-- Active customers sorted by name
SELECT id, companyname, email
FROM customer
WHERE isinactive = 'F'
ORDER BY companyname ASC
LIMIT 10

Step 4: Use in SuiteScript

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

    const onRequest = (context) => {
        // Run SuiteQL query
        const results = query.runSuiteQL({
            query: `
                SELECT id, companyname, email
                FROM customer
                WHERE isinactive = 'F'
                ORDER BY companyname
                LIMIT 10
            `
        }).asMappedResults();

        // Results is an array of objects
        results.forEach(customer => {
            log.debug('Customer', customer.companyname);
        });
    };

    return { onRequest };
});

---

Finding Table and Column Names

Method 1: Records Catalog

1. Go to Setup > Records Catalog
2. Search for record type
3. View available fields and their IDs

Method 2: Analytics Browser

1. Go to Analytics > Analytics Browser
2. Browse tables and columns
3. See data types and relationships

Method 3: Schema Query

-- List all columns in customer table
SELECT *
FROM customer
WHERE 1 = 0  -- Returns no data, just schema

---

Common Record Types and Tables

Transaction Tables

Transaction	Type Value	Example Filter
Sales Order	SalesOrd	type = 'SalesOrd'
Invoice	CustInvc	type = 'CustInvc'
Purchase Order	PurchOrd	type = 'PurchOrd'
Vendor Bill	VendBill	type = 'VendBill'
Cash Sale	CashSale	type = 'CashSale'
Journal Entry	Journal	type = 'Journal'

Entity Tables

Entity	Table Name
Customer	customer
Vendor	vendor
Employee	employee
Contact	contact
Partner	partner

Item Tables

Item Type	Table
All Items	item
Inventory	item (with type filter)
Service	item (with type filter)
Assembly	item (with type filter)

---

Query Best Practices

Do's

-- DO: Select only needed columns
SELECT id, companyname, balance
FROM customer

-- DO: Use LIMIT for testing
SELECT * FROM customer LIMIT 10

-- DO: Use parameterized queries in SuiteScript
query.runSuiteQL({
    query: 'SELECT * FROM customer WHERE id = ?',
    params: [customerId]
});

Don'ts

-- DON'T: Select all columns in production
SELECT * FROM transaction  -- Slow!

-- DON'T: No LIMIT on large tables
SELECT id FROM transactionline  -- Could return millions

-- DON'T: String concatenation for parameters (SQL injection risk!)
query: 'SELECT * FROM customer WHERE id = ' + customerId  -- WRONG!

---

Next Steps

Now that you understand the basics, continue learning:

1. Operators & Functions - Filtering and data manipulation
2. Joins - Combining data from multiple tables
3. Aggregation - GROUP BY and aggregate functions
4. N/query Module - Full SuiteScript integration

---

Quick Reference Card

-- Basic template
SELECT columns
FROM table
WHERE conditions
GROUP BY columns
HAVING group_conditions
ORDER BY columns
LIMIT n OFFSET m

-- Common patterns
WHERE field = 'value'           -- Exact match
WHERE field LIKE 'A%'           -- Starts with A
WHERE field IN ('A', 'B', 'C')  -- Multiple values
WHERE field BETWEEN 1 AND 100   -- Range
WHERE field IS NOT NULL         -- Not empty