retrieve fixed records from Interbase

LizaPaul
LizaPaul used Ask the Experts™
on
I am using Interbase 6. I want to extract data from an Interbase table using a SELECT statement, but i would like to restrict the number of records extracted to 100. How do I go about doing this with an SQL statement. Or is there any other way to get on with this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Stored procedures work well for this...
This one's designed to go against IB version 5 sample Employee table.


CREATE PROCEDURE GET_NEXT_RECORDS (
    ASTART_NUM INTEGER,
    ARETURN_COUNT INTEGER)
RETURNS (
    EMP_NO INTEGER,
    FULL_NAME VARCHAR (50))
AS
/* Routine to return specified number of rows  */
/* based on non-inclusive starting record number */
declare variable nCount INTEGER;
BEGIN
  /* Initialize counter */
  nCount = 0;
  /* loop over select statement */
  FOR SELECT EMP_NO, full_name FROM EMPLOYEE
   where (EMP_NO > :ASTART_NUM )
   into :EMP_NO, :FULL_NAME DO begin

      /* check counter against desired amount */
      if (nCount < :AReturn_Count ) then begin
        /* retun a row and increment counter */
        SUSPEND;
        nCount = nCount + 1;
      end
      else
        Exit;

   end
END






for usage, consider the following statement:

select * from GET_NEXT_RECORDS( -1, 10 )

It returns 10 rows from the table, with all records
having EMP_NO greater than -1.

Since the last record returned has an EMP_NO of 20,
use that for the successive statement:

select * from GET_NEXT_RECORDS( 20, 10 )

It returns 10 rows from the table, with all records
having EMP_NO greater than 20.

Correction -
this version orders the records properly, inside of the loop. ( assuming that EMP_NO is the correct order )


CREATE PROCEDURE GET_NEXT_RECORDS (
    ASTART_NUM INTEGER,
    ARETURN_COUNT INTEGER)
RETURNS (
    EMP_NO INTEGER,
    FULL_NAME VARCHAR (50))
AS
declare variable nCount INTEGER;
BEGIN
  /* Initialize counter */
  nCount = 0;
 
  /* loop over select statement of ordered rows*/
  FOR SELECT EMP_NO, full_name FROM EMPLOYEE
   where (EMP_NO > :ASTART_NUM )
   order by EMP_NO
   into :EMP_NO, :FULL_NAME DO begin

      /* check counter against desired amount */
      if (nCount < :AReturn_Count ) then begin
     
        /* retun a row and increment counter */
        SUSPEND;
        nCount = nCount + 1;
      end
      else
        /* AReturn_count has been met, terminate */
        Exit;

   end
END

Author

Commented:
Thank you for the answer, it has helped me.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial