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

Question

Question

alex chang · Apr 17, 2020

#SQL #Caché

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

Robert Cemper · Apr 17, 2020

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.

1 0

score 1 · Answer 1 · 2020-04-17T03:53:19-04:00

%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

score 1 · Answer 2 · 2020-04-17T04:15:20-04:00

Robert Cemper · Apr 17, 2020

Just as a side note.
mixing Caché tables with external tables (e.g. in a JOIN) is not possible.

1 0

score 0 · Answer 3 · 2020-04-17T04:28:26-04:00

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.

score 0 · Answer 4 · 2020-04-17T05:12:10-04:00

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 .

score 1 · Answer 5 · 2020-04-17T06:31:49-04:00

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

score 0 · Answer 6 · 2020-04-19T09:58:03-04:00

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,

score 1 · Answer 7 · 2020-04-19T14:52:57-04:00

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 }

score 0 · Answer 8 · 2020-04-19T15:53:28-04:00

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.