1. SuiteAnalytics
  2. Connect
  3. Connect Service Considerations
  4. Query Language Compliance

Query Language Compliance

Important:

Support ends for the NetSuite.com data source in 2025.1, and it will be removed in 2026.1. Start transitioning to the NetSuite2.com as soon as possible to avoid disruption when this change occurs.

When you run queries using the NetSuite2.com you must use SuiteQL. If you still use NetSuite.com, you should run standard SQL queries. See the following topics:

For more information on general syntax requirements, see Querying Data with Connect.

SuiteQL Compliance

Note:

This applies to the NetSuite2.com data source only.

SuiteQL lets you query your NetSuite data using advanced query capabilities. SuiteQL includes a list of supported SQL functions and does not allow you to use unsupported SQL functions in your query, which prevents SQL injection and other unauthorized access to data. Also, SuiteQL supports the syntax for both ANSI and non-ANSI joins. However, you cannot use both syntax types in the same query. To learn about SuiteQL and how to build SuiteQL queries, see the following help topics:

SQL Compliance

Note:

This applies to the NetSuite.com data source only. The use of NetSuite.com is no longer a best practice. You should move to the NetSuite2.com data source. For more information, see Changing from NetSuite.com to NetSuite2.com.

You should use generic SQL-92 syntax for the Connect Service, including standard SQL comments. The Connect Service may not be fully compliant with SQL-92 syntax and is limited to both OA SDK and Oracle. Take note of the following cases that may affect your queries.

We do not guarantee full SQL-92 compliance and are limited both by OA SDK and Oracle.

Non-deterministic Column Order Using the Column Selector

Using the * column selector returns a non-deterministic column order.

UNION Processing and UNION ALL

UNION query processing is LTR (Left-to-Right Subquery), as per the SQL standard. Consequently, queries that contain inner queries combined with the UNION operator may fail if the names of the selected inner queries’ columns do not match. For example, the following query will fail because the company_id and customer_id columns do not match:

              select *
from
   (
   select COMPANY_ID from NOTES_SYSTEM
   union
   select CUSTOMER_ID from CUSTOMERS
)

To prevent this failure, you can rewrite this query to use aliased column names:

              select *
from
   (select COMPANY_ID as 'ALIASED_COLUMN_1' from NOTES_SYSTEM
   union
   select CUSTOMER_ID as 'ALIASED_COLUMN_1' from CUSTOMERS
   )

The following examples of UNION ALL are supported:

              select N'' as COL1
from accounts
where account_id < 10
union all
select is_balancesheet as COL1
from accounts 
where 
account_id < 10 

select to_number(sum(openbalance))
from accounts
where account_id < 100
union all
select to_number('')
from accounts
where account_id < 100

select sum(openbalance)
from accounts
where account_id < 100
union all
select to_number('')
from accounts
where account_id < 100

Select All Rows Statements

Using 'select * from certain tables' may lead to a timeout on large data volumes. For more information, see Custom Field Limitations.

Smaller Queries

For improved performance, try using multiple smaller queries instead one long query. Also, if too many concurrent queries are run, the concurrent queries may be killed.

Nested SELECT Statements

The following Nested SELECT statements examples are supported:

              select d1.department_id, (select full_name from departments d2 where d2.department_id = d1.department_id) id from departments d1

select * from (select * from departments) d1

select * from (select department_id from departments) d1

select * from departments d1 where department_id in ( select department_id from departments d2 where d1.department_id = d2.department_id)

select d1.department_id, (select full_name from departments d2 where d2.department_id = d1.department_id) id  from departments d1 where department_id  in ( select department_id from departments d2 where d1.department_id = d2.department_id)

The following nested SELECT statements examples are not supported:

              select d1.department_id, (select full_name from departments d2 where d2.department_id = d1.department_id) id from (select department_id from departments) d1

select (select full_name from departments  d1 where d1.department_id = d2.department_id) d from departments d2

select d1.department_id, (select full_name from departments d2 where d2.department_id = d1.department_id) id  from (select department_id from departments) d1     where department_id  in ( select department_id from departments d2 where d1.department_id = d2.department_id)

Interval Types Not Supported

Connect drivers do not support interval types. You can use the following alternatives for interval types.

  • Interval Day that is applied directly to TIMESTAMP or DATE types can be replaced with a positive or negative integer.

    For example, replace [current_date - interval '7' Day] with [current_date - 7].

  • Intervals for values smaller than one day can be replaced in a similar manner by converting them to fractions of a day. However, current_date does not support HH:MM:SS, so SYSDATE with the TIMESTAMP type is preferable.

    For example, replace [current_date - interval '13' Hour] with [sysdate - 13/24] or [sysdate - 1/1.84615].

  • For values larger than one day, the function Add_Months( ) can be used. Also, the numeric parameter can be multiplied by 12 for years.

    For example, [current_date + interval '2' Year] can be replaced with [Add_Months(current_date, 12*2)].

CAST and CONVERT Function

The CAST and CONVERT functions convert a value from one data type to another. The Connect driver supports the CAST and CONVERT functionality by providing alternatives to employing character, number, and date conversions using the following methods.

Character Conversion

For example, to convert a VARCHAR type, you could use this as an alternative to the CAST and CONVERT function.

              select to_char(account_id) from REVRECSCHEDULELINES

Numeric Conversion

For a NUMBER type, you could use to_number for conversion in to a number example as an alternative to the CAST and CONVERT function.

              select to_number('10'+amount) from REVRECSCHEDULELINES

Date Conversion

For a DATE conversion, you could use to_date for conversion in to date.

              select to_date('01-01-2013', 'dd-mm-yyyy') from REVRECSCHEDULELINES

CLOB Values

CLOB values are not supported in SQL Order by clauses. To determine if a column returns CLOB values, you can use the OA_PRECISION column. The following example shows how you can use the OA_PRECISION column in a query:

              Select TABLE_NAME, COLUMN_NAME, OA_PRECISION from OA_COLUMNS where OA_PRECISION > 4000

If you need to use a column that returns CLOB values in a SQL Order by clause, you can use the substring function in your query. The following examples show how you can change your queries.

The following example is not supported. The query does not include the substring function.

              SELECT to_char(MESSAGE) FROM MESSAGE ORDER BY 1

The following example is supported. The query includes the substring function.

              SELECT to_char(substring(MESSAGE, 1, 4000)) FROM MESSAGE ORDER BY 1

Reserved Words

If you use any of the following reserved words, you must place them in quotes when using them in queries to avoid parsing errors:

  • RANK

  • ROW_NUMBER

  • STDDEV

  • STDDEV_POP

  • STDDEV_SAMP

  • VAR_POP

  • VAR_SAMP

For example:

              select "RANK" from TABLE
Note:

There are no standard NetSuite records or columns that conflict with the new reserved words. You will encounter this issue only if you have a custom record or custom column exposed through the Connect Service with a name that matches a new reserved word. Case does not matter and the conflict also occurs when the underscore is replaced with a space. For example, custom columns named both var samp (with a space) and var_samp (with and underscore) would both conflict with a new reserved word and would need to be quoted in queries.

For the following reserved words, special rules apply:

  • If you use one for the following reserved words for a SuiteAnalytics field, the field name will appear as “reserveword_0”.

  • If you use one for the following reserved words for a SuiteAnalytics table, the table name will appear as “reserveword_0”.

If you use one of the following reserve words for a SuiteAnalytics custom table or field, will causes appended suffixes to appear. For example, If you attempt to name a SuiteAnalytics custom field using a reserved word, the field will appear as "reserveword_0".

Reserved Words

Related Topics

General Notices