Adding an auto-increment field in a select statement

[Elena]

I'm writing a select statement extracting fields from several joined database files and the users are asking to add an auto incrementing field (a record counter).
I could write an easy RPG program, but I'm asking if I could avoid it.
Maybe somebody knows a way to make this into the select statement....

Thanks

[DeadManWalks]

Re: Adding an auto-increment field in a select statement

Hi, for starters you can look here;

http://www.code400.com/forum/showthread.php?t=1637

DMW

[Elena]

Re: Adding an auto-increment field in a select statement

I still had seen this but I'm writing a SELECT a, b, c, FROM myfile etc.....
I don't want to create a table......

If there's no other way I'll write an RPG program to renumerate the records.....

THX Anyway!!!

[jamief]

Re: Adding an auto-increment field in a select statement

You could try something like this...Its a bit scary cause the field justs adds and the DDS no longer matches the database.....also I couldnt get the default to work

ALTER TABLE WORKFILEP ADD COLUMN ID# 
TIMESTAMP NOT NULL WITH DEFAULT 

I also tried data type of ROWID but couldnt get to work.

http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000888.htm

[B.Hauser]

Re: Adding an auto-increment field in a select statement

Hi,

in the following example the sales table gets joined with the address table to retrieve the client's name and with the item master file to retrieve the item description. The result lists all clients with names and items with description from the sales table. A sequence no is added.
To calculate the sequence I had to create an "artificial" by concating the customer no and the item no. After I joined the "temporary" view x with itself over the artificial key. Because I didn't join with = but with >=, I could use the count(Number) as Counter.

with y as (select  Distinct a.CustNo, CustName, a.ItemNo, ItemDescr, 1 as Nbr,                   
                   Digits(a.CustNo) concat Digits(ItemNo) as Key   
              from Sales a  join Address b on a.CustNo = b.CustNo
                            join ItemMaster c on a.ItemNo = c.ItemNo)
select y.CustNo, y.CustName, y.ItemNo, y.ItemDescr, count(z.Nbr) as Counter      
  from y  inner join y as z                                         
    on y.Key >= z.Key                                               
  group by y.CustNo, y.CustName, y.ItemNo, y.ItemDescr
  order by y.CustNo, y.ItemNo 

Hope this helps

Birgitta

[Elena]

Re: Adding an auto-increment field in a select statement

IT WORKS!!!
Many thanks.

Elena