Number of Rows without Count Function

Hi, I'm trying to obtain the number of rows of a table without using the count function in Informix Universal Server 9.14. The problem is: I'm trying to update a field in a typed table (can't have serials) and I can't use it in subqueries to the table I'm updating... ---This is not possible---------------------- update table set field1 = (select count(*) from table) + 1; -------------------------------------------- There is other way to achieve this? The 'INFO Status' Command gives me the information I need, but there is no way to obtain it to process using SQL . There is an Hidden Column (like rowid) which gives me the the information I need? Has someone knows the data structure associated with a Table? Many thanks in advance for any help. -- Francisco

I can help you, ut you REALLY don't want me to. If you EVER delete a row you will get a duplicate key! In addition if you have more than one person inserting into the table they will also get duplicated values when they inquire as to the row count. (FWIW information you want is in: sysmaster.sysptnhdr.nrows which you can link to systabnames by partnum.) There is ONLY two valid ways to do what you want to do. 1) Create a table with one row and a single integer column. To update your table SELECT Serial_Col FROM Serial_Table FOR UPDATE; using a cursor which will lock the record then you: UPDATE Serial_Table SET Serial_Col = Serial_Col + 1 WHERE CURRENT OF cursor_name; to set the serial number for the next update. This solves both the problems I mentioned with your apparent plan. HOWEVER, it presents a new problem, it single threads all updates/inserts into the typed table since each task must get an update lock on this one record. The solution is the other method, vis: 2) Create a parallel table to the typed table with one row per row and a serial column. Always insert into this table first with a zero into the serial column and then you can insert/update the other table. You should have a primary key constraint on the serial column table and a foreign key constraint pointing there on the typed table. This eliminates the last problem since inserts to a serial table are not single threaded and far less expensive than plan 1. Art S. Kagel Francisco Queiros Pinto wrote: > > Hi, > > I'm trying to obtain the number of rows of a table without using > the count function in Informix Universal Server 9.14. > > The problem is: I'm trying to update a field in a typed table > (can't have serials) and I can't use it in subqueries to the table > I'm updating... > > ---This is not possible---------------------- > update table > set field1 = (select count(*) from table) + 1; > -------------------------------------------- > > There is other way to achieve this? > > The 'INFO Status' Command gives me the > information I need, but there is no way to > obtain it to process using SQL . > > There is an Hidden Column (like rowid) which > gives me the the information I need? > > Has someone knows the data structure associated > with a Table? > > Many thanks in advance for any help. > > -- > Francisco

Hi How about the update ? update table set field1=(select sp.nrows from sysmaster:sysptnhdr sp ,systables st where sp.partnum=st.partnum and st.tabname="table")+1; You can use the statement if your database has logging mode. Regards, Eugene Nechayev In article <38304CA1.5569A5B6@ukc.ac.uk>, Francisco Queiros Pinto <fqp1@ukc.ac.uk> wrote: > Hi, > > I'm trying to obtain the number of rows of a table without using > the count function in Informix Universal Server 9.14. > > The problem is: I'm trying to update a field in a typed table > (can't have serials) and I can't use it in subqueries to the table > I'm updating... > > ---This is not possible---------------------- > update table > set field1 = (select count(*) from table) + 1; > -------------------------------------------- > > There is other way to achieve this? > > The 'INFO Status' Command gives me the > information I need, but there is no way to > obtain it to process using SQL . > > There is an Hidden Column (like rowid) which > gives me the the information I need? > > Has someone knows the data structure associated > with a Table? > > Many thanks in advance for any help. > > -- > Francisco > > Sent via Deja.com http://www.deja.com/ Before you buy.