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_idreturns the same result set as:
from suppliers
INTERSECT
select distinct supplier_id
from orders;
select distinct orders.supplier_idEXCEPT example - suppliers against whom no orders have been processed:
from orders, supplier
where orders.supplier_id = suppliers.supplier_id;
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 ();In use these routines look like this:
define global counter int default 0;
let counter = 0;
end procedure;
execute procedure init_rownum();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 rownum() as rownum, tabname from systables;
select *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:
from (select rownum() as rownum, tabname from systables)
order by tabname;
http://www.mcpressonline.com/sql/techtip-combining-aggregate-and-detail-in-the-same-sql-result-set.html
