New post
Question
alex chang · Apr 17, 2020

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

#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.

0
0 453
Discussion (9)0
Log in or sign up to continue
Robert Cemper · Apr 17, 2020

%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

1 0
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
alex chang · Apr 17, 2020

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. 

0 0
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
alex chang · Apr 17, 2020

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 .

0 0
alex chang · Apr 19, 2020

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,

0 0
Robert Cemper · Apr 19, 2020

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  }
1 0
Robert Cemper · Apr 19, 2020

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.

0 0