DB2 function equivalent in Informix
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). sample data returned: 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 the developer's comment : "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 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 ) asseq_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.
On Thu, Apr 12, 2012 at 9:37 AM, <tomcaml@gmail.com> wrote:
> 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).
>
> sample data returned:
>
> 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
>
>
> the developer's comment :
> "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
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>