BizTalk Adapter for NetSuite

Build 20.0.7606

SuiteQL

Warning: The following feature is currently in a Beta on NetSuite. The implementation is very new and we are in the process of filling out features. Some features may be incomplete or missing.

SuiteQL is offered currently as a Beta feature by NetSuite. The adapter supports this connection by the Schema connection property. Simply set Schema to SuiteQL to access the service.

Differences between SuiteTalk and SuiteQL

SuiteQL offers an Oracle syntax to query data from NetSuite. The default service supported in SuiteTalk is instead designed using a number of SOAP requests to retrieve data in a format that is more closely associated with the saved searching in NetSuite. This means that SuiteQL can be used to handle much more complicated queries that SuiteTalk. For instance, joins, aggregations, group by, order by, and formulas can all be handled server side using SuiteQL. In contrast, SuiteTalk only supports some joins and predicates. In general, SuiteQL is a more powerful option for executing queries faster against NetSuite due to its more rich support for handling SQL.

Limitations of SuiteQL

As a releatively new feature in NetSuite and the CData BizTalk Adapter for NetSuite in general, SuiteQL contains a number of limitations. These include:

  • No support for Insert / Update / Delete. Only SELECT queries are supported.
  • No support for custom records / fields / transactions. While these are supportable through the SuiteQL REST API, they are not available in the CData BizTalk Adapter for NetSuite at present due to the API's limited capacity for metadata retrieval.
  • Only OAuth / Token Based Authentication is supported. The SuiteQL REST API does not support user/password authentication.
  • Not all Oracle formulas are supported in SuiteQL.

Supported Formulas

The following lists the available formulas in SuiteQL as of version 2020.1.

  • ABS
  • ACOS
  • ADD_MONTHS
  • APPROX_COUNT_DISTINCT
  • ASCII
  • ASCIISTR
  • ASIN
  • ATAN
  • ATAN2
  • AVG
  • BFILENAME
  • BITAND
  • CEIL
  • CHARTOROWID
  • CHR
  • COALESCE
  • COMPOSE
  • CONCAT
  • CORR
  • CORR_K
  • CORR_S
  • COS
  • COSH
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DECODE
  • DECOMPOSE
  • DENSE_RANK
  • EMPTY_BLOB
  • EMPTY_CLOB
  • EXP
  • EXPR
  • FLOOR
  • FROM_TZ
  • GREATEST
  • INITCAP
  • INSTR
  • LAST_DAY
  • LEAST
  • LENGTH
  • LENGTH2
  • LENGTH4
  • LENGTHB
  • LENGTHC
  • LN
  • LOCALTIMESTAMP
  • LOG
  • LOWER
  • LPAD
  • LTRIM
  • MAX
  • MEDIAN
  • MIN
  • MOD
  • MONTHS_BETWEEN
  • NANVL
  • NEW_TIME
  • NEXT_DAY
  • NLSSORT
  • NLS_INITCAP
  • NLS_LOWER
  • NLS_UPPER
  • NULLIF
  • NVL
  • NVL2
  • ORA_HASH
  • POWER
  • RANK
  • REGEXP_INSTR
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REMAINDER
  • REPLACE
  • ROUND
  • ROW_NUMBER
  • RPAD
  • RTRIM
  • SIGN
  • SIN
  • SINH
  • SOUNDEX
  • SQRT
  • SUBSTR
  • SUM
  • SYS_EXTRACT_UTC
  • TAN
  • TANH
  • TO_BINARY_DOUBLE
  • TO_BINARY_FLOAT
  • TO_CHAR
  • TO_CLOB
  • TO_DATE
  • TO_MULTI_BYTE
  • TO_NCHAR
  • TO_NCLOB
  • TO_NUMBER
  • TO_SINGLE_BYTE
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TRANSLATE
  • TRUNC
  • TZ_OFFSET
  • UNISTR
  • UPPER
  • VSIZE
  • WIDTH_BUCKET

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7606