SuiteQL Limitations and Exceptions

The following are known limitations and exceptions when using SuiteQL:

• You can create SuiteQL queries using the syntax for either SQL-92 or Oracle SQL, but you cannot use both syntaxes in the same query. To learn about SuiteQL syntax and grammar, see SuiteQL Syntax and Examples.

• 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 casing of record type names and field names in SuiteQL query results may not always be consistent and can change. For example, the current name of the account record type is account, but in query results, this name could appear as Account or ACCOUNT. These name changes may occur after a NetSuite release or scheduled e-fix.

                SELECT * FROM account 
  
              

                SELECT * FROM Account 
  
              

                SELECT * FROM ACCOUNT 
  
              

  When you work with query results, make sure that you do not depend on name casing. You can convert these names to uppercase or lowercase to ensure consistent casing.

  When you run SuiteQL queries using the N/query module, the query results may not reflect the casing used to build the query. For example, you can use aliases (the AS keyword) in your query string. However, the query results do not reflect the casing of the aliases you specify, and the aliases appear in lowercase. Consider the following:

                query.runSuiteQL({ query: 'SELECT id AS myID FROM transaction'
  }).asMappedResults(); 
  
              

  This method runs the specified SuiteQL query string and returns the query results as mapped results. In the array of mapped results, the key appears as myid instead of myID. For more information, see query.runSuiteQL(options) and ResultSet.asMappedResults().

  When you run constructed queries (those you create using the query.create(options) method in the N/query module), you do not need to consider potential casing changes. For constructed queries, you should specify the query type using values from the query.Type enum. If you specify the query type as a string directly and do not use values from this enum, the query type is processed in a case-insensitive way, and your query will still run correctly.

• BUILTIN.HIERARCHY(PARENT, 'LEVEL') can sometimes produce inconsistent results if the depth of the parent field is directly connected to the base order. The level value will be taken from n-1 instead of n, where n represents the depth of the parent. For more information, see SuiteQL Supported Built-in Functions.

• To mitigate the possibilities of a CSV injection, the following formatting is applied to all dataset and workbook results that are exported to CSV using SuiteScript and SuiteQL:

  • All text values are enclosed in double quotation marks

  • Values that begin with the characters -, +, =, @, tab, and EOL (end of line) have single apostrophes added in front of the value

• Listing application data from custom fields is not supported with Analytic APIs. For example, if you run a SuiteQL query on a custom transaction body field, it will not return any information from its corresponding subtabs in the UI. For more information about custom fields, see Custom Fields.

• Sorting results based on a field that contains CLOB (character large object) values only evaluates the first 250 characters for each result. Results with more than 250 characters may not be sorted properly.

• Queries that use the transaction type (TranType) field in a condition for custom transaction status (TransactionStatus) records can sometimes return unexpected results. The following example attempts to retrieve a custom transaction type for a transaction status record, but can sometimes return inconsistent results.

                SELECT * FROM TransactionStatus WHERE TranType = 'CuTrSale' 
  
              

  As a workaround, consider using the LIKE operator in your query as in the following example.

                SELECT * FROM TransactionStatus WHERE TranType LIKE 'CuTrSale%' 
  
              

General Notices