Essential syntax requirements that must be followed when writing SuiteQL queries, particularly when using the Connect Service. Understanding these rules will help ensure that your queries execute correctly and efficiently within NetSuite.
When writing queries using SuiteQL with the Connect Service, follow these important syntax rules to avoid errors:
String Concatenation
- The + operator cannot be used for concatenating strings.
- Instead, use the || operator for both field concatenation and literal concatenation.
- Example:
SELECT firstName || ' ' || lastName AS fullName FROM customer;
- This correctly combines firstName and lastName into a single fullName field.
IN Clause Argument Limit
- A single IN clause cannot contain more than 1,000 arguments.
- If you need to check against a large dataset, consider using multiple queries or JOINs instead.
- Example of an Invalid Query:
SELECT * FROM customer WHERE id IN (1, 2, 3, ..., 1001);
- Alternative Solution: Use multiple IN clauses with OR or optimize with subqueries.
WITH Clauses Are Not Supported
- The WITH clause (also known as Common Table Expressions, or CTEs) cannot be used in SuiteQL.
- Instead, use subqueries or temporary tables to structure your data.
Date Literals Must Use TO_DATE()
- SuiteQL does not support direct date literals (e.g., ‘2024-01-01’).
- Instead, wrap dates in the TO_DATE() function to ensure compatibility.
- Example:
SELECT * FROM transactions WHERE transactionDate = TO_DATE('2024-01-01', 'YYYY-MM-DD');
Right Outer Joins Are Not Supported
- SuiteQL does not allow right outer joins (RIGHT JOIN).
- If you need the same result, rewrite the query using a left outer join (LEFT JOIN) instead.
- Invalid Query (Oracle-style right outer join):
SELECT a1.id FROM account a1, account a2 WHERE a1.id (+) = a2.id;
- Alternative Using LEFT JOIN:
SELECT a2.id FROM account a1 LEFT JOIN account a2 ON a1.id = a2.id;
- Additionally, while SuiteQL supports both ANSI and non-ANSI join syntax, you cannot mix them within a single query.
Field Names in Subqueries Should Not Be Quoted
- Do not enclose field names in quotation marks (“”) when using them in subqueries.
- Quotation marks can cause errors in SuiteQL.
- Incorrect Syntax (Using Quotation Marks):
SELECT * FROM (SELECT a.externalid "AccountId" FROM account a);
- Correct Syntax (Without Quotation Marks):
SELECT * FROM (SELECT a.externalid AccountId FROM account a);