How to issue correct paging query to get dataset from external database
Hi,
I issue a sql like follow:
select *, %vid from (select * from order_info) where %vid between 1 and 10
I got error
[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <Remote JDBC error: ORA-00911: invalid character >]
So ,what's the correct paging syntax for query external data?
Tks.
%VID is a Caché internal workaround for subqueries to hide the fact that ROWNUM or ROW_NUMBER wasn't implemented
https://cedocs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=RSQL_C189621
You can't mix it with external database access with internal features
instead, use the RowNumber implementation of your external DB (they vary by product)
https://www.w3schools.com/sql/sql_top.asp
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15
You just can win. The workload moves to the external DB and you transfer fewer records
Just as a side note.
mixing Caché tables with external tables (e.g. in a JOIN) is not possible.
Thank You, My database is oracle 11g
It use ROWNUM as paging condition ,like this
select * from order_info where ROWNUM <=3
But above query can't execute because follow error in ensemble sql console
Not found ROWNUM field in table order_info
ROWNUM is a fake field for oracle like %VID in Caché
seems like the query external datase's flow is Caché parse -> sql gateway, It cause above problem.
So a possible workaround could be to have a VIEW on Oracle including ROWNUM as a column
like SELECT ROWNUM as row, * from whatever_table
and then map the View instead of the original table just for this purpose.
It's a choice, but not the best solution.
Because on view the problem still existed. So I need modify old view.
Does have any other options?
Thank You .
I'm not an expert on Oracle and have no chance to test. Just an idea
Instead of ROWNUM the function ROW_NUMBER() should pass the syntax check as it is not a column identifier.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
I tryed ROW_NUMBER() ,got follow error:
Mistake in SQL statement, ERROR #5540: SQLCODE: -359 Message: User defined SQL function 'SQLUSER.ROW_NUMBER' does not exist
The problem is not what I use either ROW_NUMBER() or ROW_NUM fake field.
The point is the flow .Cache first find ROW_NUMBER() in self platform but platform not contain oracle function.
Or is it platform's limit? I don't know where is the border..
Thank You,
Though it seems nonsense to me you can fake SQLUSER.ROW_NUMBER
for that purpose like this:
In your namespace create this class to simulate ROW_NUMBER()
{ ClassMethod RowCount() As %Integer [ SqlName = ROW_COUNT, SqlProc ]
{ quit 1 }
Sorry, it didn't work with postgreSQL.
The error changed to a gateway error.
You can map stored procedures over the Gateway but not SQL functions:
I assume the same is happening also with Oracle.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue