1. SuiteAnalytics
  2. Workbook
  3. Analytics Data Source Overview
  4. SuiteQL
  5. Using SuiteQL
  6. Using SuiteQL with the Connect Service

Using SuiteQL with the Connect Service

You can use SuiteQL to query your NetSuite data through the Connect Service. To do so, you must consider the following:

Syntax Requirements

As you create queries using SuiteQL and the Connect Service, consider the following syntax requirements:

  • For string concatenation, you cannot use the + operator. You should use the || operator instead. This restriction applies to both field and literal concatenation.

  • You cannot use more than 1000 arguments in a single IN clause.

  • You cannot use WITH clauses in your queries.

  • You cannot use date literals. You must encapsulate dates using the to_date() function.

  • You cannot use right outer joins. For example, the following Oracle SQL is not valid in SuiteQL:

                    select a1.id from account a1, account a2 where a1.id (+) = a2.id

  • SuiteQL supports the syntax for both ANSI and non-ANSI joins. However, you cannot use both syntax types in the same query.

  • Do not use quotation marks for field names in subselections. See the following examples:

    • You should not use the following syntax with quotation marks:

                          select * from ( select a.externalid "AccountId" from account a)

    • You should use the following syntax without quotation marks:

                          select * from ( select a.externalid AccountId from account a)

For more information, see Query Language Compliance.

Querying the Analytics Data Source

To find all record types and fields that are currently available for the analytics data source schema, you can do the following:

  • Use the OA_COLUMNS, OA_TABLES, and OA_FKEYS database tables. The database tables are only available through SuiteAnalytics Connect service and is not supported in SuiteQL with SuiteScript. To learn about the tables, see SuiteAnalytics Connect System Tables.

  • To find the names of record types and fields, see Record Types and Fields.

The analytics data source is not accessible for some roles and permissions. For more information, see Role and Permission Considerations for NetSuite2.com.

Unsupported Features in SQL-92

When using SQL-92 syntax, there are several features that are not supported, including embedded null values in CHAR fields, DEFAULT clauses for column values, and subqueries in SELECT lists.

Supported and Unsupported Functions

There are several functions that you can use when you run queries using SuiteQL. For a full list of supported and unsupported functions, see SuiteQL Supported and Unsupported Functions.

Supported Built-in Functions

You can use built-in functions to perform certain operations in SuiteQL queries. For a full list of supported built-in functions, see SuiteQL Supported Built-in Functions.

Related Topics

General Notices