How to Implement Oracle rownum in Informix

Many people who are familiar with Oracle usually encounter various questions when starting to use the Informix Database: Informix is not supported, and Informix does not have this function? In fact, many times Informix is implemented in different ways, or similar functions can be implemented through development.

The following section describes how to implement some application functions of rownum in Oracle in Informix.

We all know that the Oracle database has a pseudo-column rownum, which returns the number of sequences starting from 1. It can be used to complete the first N records or perform paging operations.

Oracle SQL is as follows:

select * from sometable where rownum <= 100;select * from sometable where rownum > 100 and rownum <= 200;SELECT * FROM ( SELECT A.*,ROWNUM AS RN FROM (SELECT * FROM sometable order by col) A WHERE ROWNUM <=  200) T WHERE T.RN > 100

Informix provides simpler and more efficient paging functions:

 select first 100 * from sometable; select  skip 100 first 100 * from sometable; select skip 100 first 100 * from sometable order by col;

Record Number:

In Oracle, you can use rownum to obtain a number for each record,

select rownum, * from sometable ;

In Informix, you need to create a stored procedure to implement similar functions.

CREATE FUNCTION  rownum () returning int as rownum;  define global counter int default 0;  let counter = counter + 1;  return counter;end function;          CREATE PROCEDURE  init_rownum ();  define global counter int default 0;  let counter = 0;end procedure; 

You can use the function inverse query record number as follows.

Execute procedure init_rownum (); -- if you want to execute rownumselect rownum () as rownum, tabname from orders Ables multiple times in the same session, select * from (select rownum () as rownum, tabname from orders Ables) order by tabname;

Note: If you want to execute rownum multiple times in the same session, you must first execute:

execute procedure init_rownum();

Returns the counter to zero.

Note: This article reproduced from: http://www.informixchina.net/home/space.php? Uid = 201 & Do = Blog & id = 2079