0

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63
Daniel Caban
  • 111
  • 1
  • 3

2 Answers2

1

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

SET LIMIT 100
SELECT * FROM CUSTOMER;
SET LIMIT OFF
GMB
  • 216,147
  • 25
  • 84
  • 135
0

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 )

Steve Leighton
  • 790
  • 5
  • 15
  • In the current environment setup I'm dealing with I'm using python and pyodbc to connect to the gupta database using Easysoft's ODBC-ODBC bridge software. The isql binary on linux doesn't recognize session variables for some reason (can't set limit 100) and the prepare keyword is also not recognized. It seems like prior to version 11.7 there's no way to limit results in a standard SQL interface type of way. The only ways seem to be to interact with SQLTalk or TeamDeveloper – Daniel Caban Feb 24 '20 at 16:41
  • Don't know Python --> but maybe you can just fetch one record at a time in a loop with a count and stop after count = 100. Obviously an Order By will give you the top 100 you need. – Steve Leighton Feb 25 '20 at 09:39