In search of rownum

Often people ask about various features supported by other RDBMS systems that Informix does not support.  I am rarely bothered by these issues, not because I don't use Oracle, MySQL, PostgreSQL, or MS SQL Server - I don't when I can help it - but rather because for the most part, the required functionality is either there in another form, is not really necessary, or can be emulated easily enough.

Examples are the INTERSECT and EXCEPT join operators.  Informix does not support either syntax. However, the results of an INTERSECT join are identical to the results of a normal INNER join with only columns from one of the tables included in the projection clause which is of course supported. The results of an EXCEPT are identical to those of a LEFT OUTER JOIN with only those values from the left table that do not match any rows in the right table returned.

INTERSECT example - suppliers against whom at least one order has been processed:

select distinct supplier_id
from suppliers
INTERSECT
select distinct supplier_id
from orders;

returns the same result set as:

select distinct orders.supplier_id
from orders, supplier
where orders.supplier_id = suppliers.supplier_id;

EXCEPT example - suppliers against whom no orders have been processed:

select distinct supplier_id
from suppliers
except
select distinct supplier_id
from orders;

returns the same result set as:

select suppliers.supplier_id
from suppliers
left outer join orders
on suppliers.supplier_id = orders.order_id
where orders.order_id is null;

Another is the Oracle ROWNUM pseudo column.  It returns a sequential number, starting from 1, indicating the order in which a row was processed by the server.  Most commonly it is used in the WHERE clause of a query to limit the number of rows returned or to walk through a result set in discrete chunks. 
Examples:

select * from sometable where rownum <= 100;

select * from sometable where rownum > 100 and rownum <= 200;

These are easy to emulate using the ANSI FIRST and SKIP verbs which Informix does support:

select first 100 * from sometable;

select first 100 skip 100 * from sometable;

However, sometimes ROWNUM is used in the projection clause of a query where it represents the selection order as a computed column.  To emulate this use, we need to create two SPL routines.  The procedure init_rownum() below initializes the value returned by the next call to the rownum() function to '1' and is needed if rownum() is going to be used more than once in a session and is expected to return values starting with '1' each time.  Here are the routine definitions:

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;                  

In use these routines look like this:

execute procedure init_rownum();
select rownum() as rownum, tabname from systables;

Now suppose you want to sort the tabnames by name but return the rownum of the original order that the rows were processed by the engine:

select *
from (select rownum() as rownum, tabname from systables)
order by tabname;

That's it, nothing profound today, just this little tidbit of technique.  Oh!  Don't forget to check out my TechTips article about Derived Tables in McPress at:

http://www.mcpressonline.com/sql/techtip-combining-aggregate-and-detail-in-the-same-sql-result-set.html