Re: dynamic row sequence
Cesar Inacio Martins wanted a dynamic row sequence number in a query. Holger de Wall suggested ROWID, which Cesar rejected as non-sequential. Doug Lawry posted an SPL function using a global variable to return incrementing numbers, but it needs manual starting and has caveats with GROUP BY or ORDER BY. Cesar said that did not work for him either. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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@dewall-net.de> escreveu:
De: Holger de Wall <holger@dewall-net.de>
Assunto: Re: dynamic row sequence
Para: informix-list@iiug.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/infocenter/idshelp/v115/index.jsp?tab=search&searchWord=rowid
select ROWID, name from users where state = 'NY';
--
Holger
Am 10.09.2010 20:45, schrieb Cesar Inacio Martins:
> 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
>
>
>
>
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
Informix-list@iiug.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@nildram.co.uk> escreveu:
De: Doug Lawry <lawry@nildram.co.uk>
Assunto: Re: dynamic row sequence
Para: informix-list@iiug.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@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list