I’m trying to find out if anyone is aware of any list of the limitations of NetSuite’s ODBC SuiteAnalytics Connect Service being used through OpenQuery? I’ve been running into many problems with some of my more complex queries failing for no apparent reason, and I finally started narrowing it down to some sort of strange limitations that I wasn’t expecting from an Oracle ODBC. Here’s a few examples that I’ve found so far:
- Can’t use a basic CTE
Basic Oracle Query:
Basic Query as CTE in SQL Server:
Basic Query as CTE in Oracle NetSuite via OpenQuery (note the unhelpful response saying basically (“don’t work”):
- Can’t use the TRUNC(date) function
Selecting SYSDATE:
Selecting and truncating SYSDATE (note the error, even though according to Oracle, the format parameter is OPTIONAL:
Selecting and truncating SYSDATE using both apparently required parameters (using a valid “format” mask from Oracle’s page). Note the unhelpful error of “TRUNC returned error”:
- Can’t use LISTAGG() function
Basic query for items’ full names:
Same query while attempting to use Oracle’s correct format for LISTAGG, note again the unhelpful “Syntax error in SQL statement”:
- Subqueries that work fine on their own seem to fail when inserted inside of a larger query for some reason (examples to come Soon™)
I’m trying to understand these limitations and learn what they are, but it’s like poking at a black box!
Advertisement
Answer
As a followup to this for anyone that comes after me…
I have contacted NetSuite directly, since I could find an answer nowhere else, and they informed me that they only support methods and objects that existed in SQL-92.
Here is their response:
According to our Project Manager,users are not connecting to an Oracle Database >directly. SuiteAnalytics Connect uses its own (virtual) schema that can be queried >in a way that is compliant with SQL-92.
Some Oracle-specific functions work, too, but is not guaranteed to work properly. >Users are discouraged from using queries that depend on version of Oracle DB.
So, while it’s very frustrating, it looks like anyone that wants to do anything fancy with NetSuite’s SQL implementation can’t use anything that’s not already existent in SQL-92 or lower. CTEs and ListAgg() therefore, are out.