Oninit cdi / Archive
Talk to an expert →

Re: dynamic row sequence

Posted in 2010

Topics: General Discussion

Cesar Inacio Martins — — source: Usenet: comp.databases.informix

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

↪ replying to Cesar Inacio Martins
Doug Lawry — — source: Usenet: comp.databases.informix

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;

↪ replying to Doug Lawry
Doug Lawry — — source: Usenet: comp.databases.informix

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;

↪ replying to Doug Lawry
Cesar Inacio Martins — — source: Usenet: comp.databases.informix

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