Question
· Feb 24, 2017

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

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ê

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

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?

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.