I'm putting the final touches on migrating queries from the "NetSuite.com" data source to "NetSuite2.com", and hitting a weird error where the query fails instantly when removing specific filters but not altering the query in any other way. It doesn't try to run and timeout, it errors instantly. If either specific filter is active, it runs correctly.
I tested with a specific AccountingPeriod and account filters, and either variation of those worked correctly. And I could remove either Accountingperiod or Account filter and it worked, but it failed if i removed both. The original query returns 60k rows in 30 seconds using the original query and and connection so I know its not a large dataset.
Is there some sort of pre-query timeout behavior? How can a query be rejected simply by removing a filter and not altering the query structure?
I'm using the JDBC SuiteAnalytics Connect driver 8.1.0.0183 through my IDE, but I experienced the same behavior using a browser-based SuiteQL IDE.
IDE error = [S1000][400] [NetSuite][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# mm539ozzvc5kpoor5dxl[400]
browser error = "Search error occurred: Invalid or unsupported search"
SELECT A.acctNumber AS accountnumber,
A.accountsearchdisplaynamecopy AS account_name,
PAA.subsidiary as subsidiary,
AP.enddate AS month_ending,
CASE WHEN TO_NUMBER(A.acctNumber) BETWEEN 0 AND 39999 THEN NULL ELSE D.name END as department,
CASE WHEN TO_NUMBER(A.acctNumber) BETWEEN 0 AND 39999 THEN NULL ELSE C.name END as class,
A.acctType AS type,
SUM(PAA.amount) AS amount
FROM postingAccountActivity PAA
INNER JOIN Account A ON PAA.account = A.id
INNER JOIN accountingPeriod AP ON PAA.accountingperiod = AP.id
LEFT JOIN classification C ON PAA.class = C.id
LEFT JOIN department D ON PAA.department = D.id
WHERE PAA.subsidiary = 1
and PAA.amount <> 0
-- and PAA.accountingperiod = 221 — WORKS if this line is active OR below
-- and a.acctnumber = '73100' — WORKS if this line is active OR above
GROUP BY A.acctNumber,
A.accountsearchdisplaynamecopy,
PAA.subsidiary,
AP.enddate,
CASE WHEN TO_NUMBER(A.acctNumber) BETWEEN 0 AND 39999 THEN NULL ELSE D.name END,
CASE WHEN TO_NUMBER(A.acctNumber) BETWEEN 0 AND 39999 THEN NULL ELSE C.name END,
A.acctType
HAVING SUM(PAA.amount) <> 0