dynamic row sequence
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
How about the following:
create function rnum(a integer) returning integerdefine global cnt int default 0;
let cnt = a;
return a;
end function;
create function rnum() returning integerdefine global cnt int default 0;
let cnt=cnt+1;
return cnt;
end function;
execute function rnum(0);select rnum() , tabname from systables;
rnum 1
tabname systables
rnum 2
tabname syscolumns
rnum 3
tabname sysindices
rnum 4
tabname systabauth
rnum 5
tabname syscolauth
rnum 6
tabname sysviews
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/10/2010 11:45:20 AM:
> [image removed]
>
> dynamic row sequence [21230]
>
> Cesar Inacio Martins
>
> to:
>
> ids
>
> 09/10/2010 11:47 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> 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
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi John,
Thanks for this...
I already try this few months ago, and works fine for the first SQL, But for
the second SQL on the same session the sequence don't restart.
I want to avoid manually restart the counter calling with some dummy execute
or sql.
Then I tried some logic way using a parameter like -1 to restart inside of the
SQL, don't work.. I don't remember why...
I just not found any way to keep the code compatible with others vendors
databases..
My last hope is write a C function to do this, but I don't check if is
possible, yet...
Cesar
--- Em sex, 10/9/10, John Miller iii <miller3@us.ibm.com> escreveu:
De: John Miller iii <miller3@us.ibm.com>
Assunto: Re: dynamic row sequence [21231]
Para: ids@iiug.org
Data: Sexta-feira, 10 de Setembro de 2010, 18:13
How about the following:
create function rnum(a integer) returning integerdefine global cnt int default 0;
let cnt = a;
return a;
end function;
create function rnum() returning integerdefine global cnt int default 0;
let cnt=cnt+1;
return cnt;
end function;
execute function rnum(0);select rnum() , tabname from systables;
rnum 1
tabname systables
rnum 2
tabname syscolumns
rnum 3
tabname sysindices
rnum 4
tabname systabauth
rnum 5
tabname syscolauth
rnum 6
tabname sysviews
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/10/2010 11:45:20 AM:
> [image removed]
>
> dynamic row sequence [21230]
>
> Cesar Inacio Martins
>
> to:
>
> ids
>
> 09/10/2010 11:47 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> 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
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.