DB2 function equivalent in Informix

Art Kagel

2012-04-12 14:30:25 UTC

Informix doesn't have such a function, but here's one that will work:

create function row_num( key varchar(255) ) returning int;
global l_key varchar(255) default NULL;
global l_ret int default 0;

if (key = l_key) then
let l_ret = l_ret + 1;
else
let l_ret = 0;
end if
return l_ret;
end function;

create procedure row_num_init();
let l_key = null;
let l_ret = 0;
end procedure;

Then you can:

select trailer, tour_date, location, row_num( trailer || tour_date ) as
seq_num
from trailer_table
order by trailer, tour_date, seq_num;

The row_num_init() function is only needed before reusing the function for
another query with a similar sequencing key just in case the first key of
hte new query data set happens to match the last key from the previous
use. Haven't tested this (just shut my linux system down to move it), so
it may need a tweak or two.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

Post by t***@gmail.com
hello,
one of our developers wanted to use a function natively in Informix that
behaves like DB2's row_num function.
seq_num would be returned from the function (sequential between trailers).
In db2 it is row_number() over(partition by trailer order by
trailer,tour_date).
trailer# tour date location seq_num
123 2/1/2012 Ardmore 1
123 2/5/2012 P&G - Dallas 2
123 2/6/2012 Dallas 3
123 2/8/2012 Ardmore 4
456 2/1/2012 Ardmore 1
456 2/4/2012 Ardmore 2
456 2/10/2012 DGStore - Dallas 3
456 2/20/2012 Ardmore 4
"Comes in handy on the db2 side when creating from-to date ranges.
I am a little surprised it isn't in informix since all the other major db
systems (db2, SAP, Oracle) have something similar."
i told him that it could be we are missing something and said i'd try to
get feedback through post here.
thanks in advance - Tom
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list