Hi,
I'm going to implement batching read, and want to ensure that I get all data if bookmark field isn't unique. so I have to implement WITH TIES algorithm.
But I cant run any of the listed approaches.
Most of these approaches should work for any version of Oracle, but don't work for NetSuite.
solution 1:
SELECT TOP 5 WITH TIES *
FROM [ACCOUNTS]
ORDER BY [DATE_LAST_MODIFIED] ASC;
syntax error. ( 'TOP N' - works, 'TOP N WITH TIES' - doesn't)
solution 2:
SELECT *
FROM [ACCOUNTS]
ORDER BY [DATE_LAST_MODIFIED] ASC;
FETCH FIRST 5 ROWS WITH TIES
syntax error.
solution 3:
SELECT *
FROM [ACCOUNTS]
WHERE [DATE_LAST_MODIFIED] <= (
SELECT [DATE_LAST_MODIFIED]
FROM [ACCOUNTS]
WHERE rownum = 5
ORDER BY DATE_LAST_MODIFIED ASC
)
error: ORDER BY clause is invalid in views, derived tables, and subqueries unless
Also tried ROW_NUMBER OVER (ORDER BY ...) :
ERROR: ERROR [S1000] [NetSuite][ODBC 64bit driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# ktswdexkzijkucrwjct2[400]
solution 4:
probably, something with variables, like:
SELECT [DATE_LAST_MODIFIED]
INTO ?lastvalue
FROM [ACCOUNTS]
WHERE rownum = 5
ORDER BY DATE_LAST_MODIFIED ASC
SELECT *
FROM [ACCOUNTS]
WHERE [DATE_LAST_MODIFIED] <= :lastvalue
but I unable to run more than one sql statement in the query.
solution 5:
WITH qwe AS (
SELECT max([DATE_LAST_MODIFIED])
FROM [ACCOUNTS]
WHERE rownum <= 5
ORDER BY DATE_LAST_MODIFIED ASC
)
SELECT *
FROM [ACCOUNTS] a
INNER JOIN qwe ON a.[DATE_LAST_MODIFIED] <= qwe.[DATE_LAST_MODIFIED]
syntax error.
I'm using ODBC connection in .Net client(C#)
please suggest
