Re: DB2 function equivalent in Informix

Simple implementations of row_num can be implemented ( http://informix-technology.blogspot.com/2012/01/udrs-rownum-in-informix-rownum-e m.html) But if you need partition by and order by the situation is more complex... The ORDER BY inside this functions is impossible to implement as an UDR. You could however simulate it with an inline view with order by and a customized function (I could provide an example), but it would not be a pretty thing from a performance point of view. In order to implement partition by you'd have to alter the UDR to receive the partition by field and keep it in memory also... a chnage in that would mean a counter restart. The order as written above would have to be forced with an ORDER BY clause on an inside view, from which you'd then SELECT (with another ORDER BY if needed). You don't mention the version you're using. Currently there is no Informix version available that has this out of the box... It can change in the future. Are you on 11.7? Regards. On Thu, Apr 12, 2012 at 2:37 PM, <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 > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --485b397dcc9737ae9a04bd7dd01c