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

Hi,

"... In Dynamic Server, you can use the rowid column that is associated
with a table row as a property of the row. ..."
from
http://publib.boulder.ibm.com/infoce...archWord=rowid

select ROWID, name from users where state = 'NY';

--
Holger

Am 10.09.2010 20:45, schrieb Cesar Inacio Martins:

Hi Holger,
Thanks for your answer, but isn't this what I looking.
Row id isn't sequencial numbers.. don't solve my problem...

Cesar

--- Em sex, 10/9/10, Holger de Wall <holger (AT) dewall-net (DOT) de> escreveu:

De: Holger de Wall <holger (AT) dewall-net (DOT) de>
Assunto: Re: dynamic row sequence
Para: informix-list (AT) iiug (DOT) org
Data: Sexta-feira, 10 de Setembro de 2010, 16:27

Hi,

"... In Dynamic Server, you can use the rowid column that is associated
with a table row as a property of the row. ..."
from
http://publib.boulder.ibm.com/infoce...archWord=rowid

select ROWID, name from users where state = 'NY';

--
Holger

Am 10.09.2010 20:45, schrieb Cesar Inacio Martins:
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

Try this:

CREATE FUNCTION spl_sequence
(start_value INTEGER DEFAULT NULL)
RETURNING INTEGER;

-- Doug Lawry, comp.databases.informix, 11/09/2010
-- Usage: SELECT spl_sequence(), * FROM ...
-- Optional: EXECUTE FUNCTION spl_sequence(?)
-- Bugs: GROUP BY or ORDER BY unless indexed

DEFINE GLOBAL spl_sequence INTEGER DEFAULT 1;

IF start_value IS NOT NULL THEN
LET spl_sequence = start_value - 1;
RETURN NULL;
ELSE
LET spl_sequence = spl_sequence + 1;
RETURN spl_sequence;
END IF

END FUNCTION;

Wih global default corrected:

CREATE FUNCTION spl_sequence
(start_value INTEGER DEFAULT NULL)
RETURNING INTEGER;

-- Doug Lawry, comp.databases.informix, 11/09/2010
-- Usage: SELECT spl_sequence(), * FROM ...
-- Optional: EXECUTE FUNCTION spl_sequence(?)
-- Bugs: GROUP BY or ORDER BY unless indexed

DEFINE GLOBAL spl_sequence INTEGER DEFAULT 0;

IF start_value IS NOT NULL THEN
LET spl_sequence = start_value - 1;
RETURN NULL;
ELSE
LET spl_sequence = spl_sequence + 1;
RETURN spl_sequence;
END IF

END FUNCTION;

Thanks Doug,
But what I trying avoid is the requirement of manually start the sequence....
this way don't work for me..

--- Em sáb, 11/9/10, Doug Lawry <lawry (AT) nildram (DOT) co.uk> escreveu:

De: Doug Lawry <lawry (AT) nildram (DOT) co.uk>
Assunto: Re: dynamic row sequence
Para: informix-list (AT) iiug (DOT) org
Data: Sábado, 11 de Setembro de 2010, 9:04

Try this:

CREATE FUNCTION spl_sequence
* * (start_value INTEGER DEFAULT NULL)
* * RETURNING INTEGER;

* * -- Doug Lawry, comp.databases.informix, 11/09/2010
* * -- Usage: SELECT spl_sequence(), * FROM ...
* * -- Optional: EXECUTE FUNCTION spl_sequence(?)
* * -- Bugs: GROUP BY or ORDER BY unless indexed

* * DEFINE GLOBAL spl_sequence INTEGER DEFAULT 1;

* * IF start_value IS NOT NULL THEN
* * * * LET spl_sequence = start_value - 1;
* * * * RETURN NULL;
* * ELSE
* * * * LET spl_sequence = spl_sequence + 1;
* * * * RETURN spl_sequence;
* * END IF

END FUNCTION;
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list