Hi People,

After a new developer ask me about a compatible code for row_number from SqlServer I get my old code, very similar of suggested here in the IIUG list and CDI and start to work over it again.. and now a idea just popup on my head and I got a solution, dirty and ugly. but works and the change of the code is minimum...
I don't see any problem or restriction with this code, if you see.. please,tell me!

Still keep in my TO DO list convert this to C function to try keep the syntax 100% of compatible with SqlServer.

well, there is the code...

Database selected.

drop procedure row_number;
Routine dropped.

create procedure row_number(initial integer) returning integer as row_number;
define global i integer default 0;
* if initial = 0 then
*** let i = 0;
*** return 0 ;
* end if;
* let i = i+1;
** return i ;
end procedure
document "Simplified and similar function to row_number() from SqlServer.",
"Need to reset the counter before each sql statement with ",
"execute procedure row_number(0) OR declare into FROM clause",
"For use in SELECT statement use any other number in parameter:",
"select row_number(1) , tabname from systables;"
"OR select row_number(1) , tabname from systables, table(row_number(0))"
with listing in "/tmp/row_number.out"
;

Routine created.

;
-- The trick is call the same function as derived table.. in FROM clause
select* first 5 row_number(1), tabname::char(20) from systables, table(row_number(0));

(expression) (expression)********

********** 1 systables**********
********** 2 syscolumns*********
********** 3 sysindices*********
********** 4 systabauth*********
********** 5 syscolauth*********

5 row(s) retrieved.


select* first 5 row_number(1), tabname::char(20) from systables, table(row_number(0));

(expression) (expression)********

********** 1 systables**********
********** 2 syscolumns*********
********** 3 sysindices*********
********** 4 systabauth*********
********** 5 syscolauth*********

5 row(s) retrieved.



Database closed.

Thanks everyone for the attention and answers...
Cesar

--- Em sex, 10/9/10, Cesar Inacio Martins <cesar_inacio_martins (AT) yahoo (DOT) com.br> escreveu:

De: Cesar Inacio Martins <cesar_inacio_martins (AT) yahoo (DOT) com.br>
Assunto: dynamic row sequence
Para: "informix-iiug" <ids (AT) iiug (DOT) org>, "informix-list cdi" <informix-list (AT) iiug (DOT) org>
Data: Sexta-feira, 10 de Setembro de 2010, 15:45

Hi,

I need a resource , what is common in any other database...

Return the sequence # of the rows returned from select, something like:

select func_rownu(), name from users
where state = 'NY';
1 john
2 katty
3 mike
4 nattan
and so on...

So , what I need is this "virtual serial/sequence" of the rows returned...
Anyone already do this ?

Cesar





*