Question
· Apr 17, 2020

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.

Discussion (9)0
Log in or sign up to continue

%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

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. 

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,