New post
Question
Andrei Luiz Nenevê · Feb 24, 2017

Is there any way of paginate SQL Query in Caché?

#Caché #ObjectScript #SQL

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

NOTE: In other SQL languages I can do, for example, SELECT * FROM extbl Limit 10 OFFSET 10

Thanks,

Andrei L. Nenevê

1
1 2,715
Discussion (8)2
Log in or sign up to continue
Dmitry Maslennikov · Feb 24, 2017

Since 2011.1 version was added %vid to achieve some paging functionality. Some more details.

SELECT *, %vid FROM (SELECT ....) v WHERE %vid BETWEEN 5 AND 10
4 1
Roman Krejci · Jan 24, 2018

I tried it. I ve got a table UACCESSRIGHT with two integer fields, one of them being RIGHTID, randomly populated.

select *, %vid from (select * from UACCESSRIGHT)  worked

however, this approach is not of much use without having the possibility to order the results, so I tried:

select *, %vid from (select * from UACCESSRIGHT order by RIGHTID)  failed on wrong syntax

select *, %vid from (select * from UACCESSRIGHT) order by RIGHTID gave result set, but a wrong one (%vid was NOT 100% correlated with RIGHTID, that is the order specified by %vid was different than order specified by RIGHTID value)

is there any usable way to paginate the ordered sets?

0 0
Robert Cemper · Jan 24, 2018
select *, %vid from (select * from UACCESSRIGHT order by RIGHTID) 
failed on wrong syntax

If you use ORDER BY in a SubSELECT you have to add a TOP clause as well. e.g.

select *, %vid from (select TOP ALL * from UACCESSRIGHT order by RIGHTID)  where %vid between 10 and 20

2 1
Roman Krejci · Jan 25, 2018

Thank you! I missed the TOP clause. It is somewhat counterintuitive, but it IS mentioned in the documentation, though.  Do you think that the form

select *, %vid from (select TOP 20 * from UACCESSRIGHT order by RIGHTID)  where %vid>=10

(which give the same result) would be more effective?

0 1
Robert Cemper · Jan 25, 2018

the only effect is a smaller resultset of the subquery.
it doesn't speed up the inner collection /sort process (+ inner  WHERE ) before TOP is applied.

I'm with you it's not intuitive and I think TOP ALL could be a default.  But that's not happening. 

1 0
Kyle Baxter · Feb 24, 2017

Yup!

DELETE FROM Sample.Person where (ID # 2) = 0   --Deletes all even numbered rows (so you know I'm not cheating and using ID numbers)

select * from (select id,name,ssn from sample.person) where %vid between 11 and 20 

%VID is a special variable in Cache SQL (VIEW ID, I think?) which allows you limit your rows without too much trouble.  And alternative is the following:

select TOP 10 ID,Name,SSN from Sample.Person where ID NOT IN (SELECT TOP 10 ID FROM Sample.Person)

The first TOP gives you your page size, and the subquery's TOP gives you where to start (in this case, we start at 11, because we're ignoring the first 10).  You can dynamically put in the sizes for TOP.  

3 0