SuiteQL Syntax and Examples
SuiteQL supports the syntax for both SQL-92 and Oracle SQL, but not all functions. Also, note that you cannot use both syntaxes in the same query. To learn about these syntaxes, see the following links:
Although SuiteQL supports ANSI SQL-92, there is a risk of critical performance issues, such as time outs that are not operationally remediable, that may occur if a SuiteQL is converted to SQL. You should use Oracle SQL syntax when creating SuiteQL queries to avoid issues. For more information, see SuiteQL Performance and Best Practices.
The following sections show you how to create queries using SuiteQL and the analytics data source.
Simple Queries
This section demonstrates how to perform simple queries using SuiteQL.
Field Values from Records
This string queries for entityid, firstname, and lastname field values in all employee records. By default, field values use the RAW field context. For more information, see query.FieldContext.
SELECT employee.entityid AS entityidRAW, employee.firstname AS firstnameRAW, employee.lastname AS lastnameRAW FROM employee
Field Values with Conditions
This string queries for entityid field values in employee records where the expenselimit of the employee is greater than or equal to 5000.
SELECT employee.entityid AS entityidRAW FROM employee WHERE employee.expenselimit >= 5000
This string queries for entityid field values in employee records where the expenselimit of the employee is greater than or equal to 5000 or the employee is a sales representative.
SELECT employee.entityid AS entityidRAW FROM employee WHERE employee.expenselimit >= 5000 OR employee.issalesrep = 'T'
Joined Records
This string queries for paytype and payrate field values in employee records joined to employee earning records where the employee is the same on both records:
SELECT paytype, payrate FROM employeeEarning earning, employee WHERE earning.employee = employee.id
Advanced Queries
The following strings demonstrate advanced queries using SuiteQL.
/* set operations */
SELECT * FROM transaction UNION SELECT * FROM transaction /* top n */
SELECT TOP 10 * FROM transaction
SELECT TOP 1 id FROM transaction UNION SELECT TOP 1 id FROM transaction /* select list */
SELECT DISTINCT email FROM transaction
SELECT (SELECT MAX(1) FROM transaction) AS one FROM transaction
SELECT COUNT(DISTINCT 1+id) FROM transaction
SELECT COALESCE(email, id, 'some value', 1 + 1) FROM transaction /* from */
SELECT * FROM transaction, transactionLine
SELECT * FROM (SELECT * FROM transaction) a INNER JOIN ((SELECT * FROM transaction UNION SELECT * FROM transaction) b INNER JOIN (SELECT * FROM transaction) c ON 1=1) ON 1=1
SELECT * FROM (SELECT id, COUNT(*) cnt FROM transactionLine GROUP BY id) WHERE cnt > 2 /* where */
SELECT * FROM transaction t WHERE id IN (SELECT id FROM transaction WHERE id = t.id UNION SELECT -1 FROM transaction)
SELECT * FROM transaction WHERE 1 = (SELECT MAX(1) FROM transaction)
SELECT * FROM transaction WHERE EXISTS(SELECT 1 FROM transaction)
SELECT * FROM transaction WHERE id IN ((SELECT MAX(1) FROM transaction), 2+1) /* having */
SELECT email, COUNT(*), MAX(create_date) FROM transaction GROUP BY email HAVING COUNT(*) > 2