Re: auto increment in select clause

On Oracle, creating a sequence and then getting its nexval for each record that gets selected by select clause solves the problem. It also eliminates the problem of duplicates and self join etc. One can find parallel usage of sequence in DB2 and MSSQL too

SQL> create sequence temp;
Sequence created.

SQL> select temp.nextval as colNumber, <column to be selected> from <table from which selection is made>

Paul Vernon wrote in message ...
>In DB2 you can use
>
>SELECT ROW_NUMBER() OVER(), ref FROM table
>
>or
>
>SELECT NEXVAL FOR sequence FROM table
>
>the former is more flexable as you can PARTITION and/or ORDER your
>sequences
>
>TTM suggests a TAG operator in RM VSS #1 which would work somewhat like
>ROW_NUMBER
>
>Regards
>Paul Vernon
>Business Intelligence, IBM Global Services
Received on Mon Jun 17 2002 - 03:58:42 CDT