Gupta SQL Base limit number of rows returned

Question

I've been looking for hours on how to return a limited set of rows similar to SELECT TOP 100 * FROM CUSTOMERor SELECT * FROM CUSTOMER LIMIT 100 or SELECT * FROM CUSTOMER WHERE ROWNUM <= 100 and for the life of me I cannot find a way to do this with this particular database.

The database version is 10.1.46 and I've found documentation for a later version of SQLBase that says the Limit keyword is what should be used, but nothing has worked when attempting to limit the amount of rows returned using isql. SQL Prepare errors get thrown. I'm at a loss as to how to do this and I'm beginning to think the database just doesn't support limiting the amount of rows returned.

I'm hoping someone knows how I can limit the number of records returned.

Answer 1

As far as concerns, limit is implemented in Base SQL using session parameters:

SET LIMIT 100
SELECT * FROM CUSTOMER;
SET LIMIT OFF

Answer 2

Alternatively to 'LIMIT', you can use 'PERFORM' then 'FETCH' in SQLTalk:

PREPARE SELECT * from CUSTOMER ORDER BY <column1> DESC;
PERFORM;
FETCH 100;

or if you are using TeamDeveloper,

Simply:

SqlPrepareAndExecute( hSql, 'SELECT * from CUSTOMER ORDER BY <column1> DESC')
While nPtr < 101
Call SqlFetchNext(hSql,nReturn)

After v11.7 you can use the following syntax:

SELECT * FROM CUSTOMER LIMIT 100

p.s. If you need SQLBase manuals for any version v8 through v12.2 go here: SQLBase Manuals ( all versions )