alex chang · Apr 17, 2020

How to issue correct paging query to get dataset from external database


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?


0 453
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 

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)

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 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()

Class User.FakeRowcount
{ 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.