Re: Informix SQL question !!!

> > Giuliano Ferraro wrote: > > > > I'm coming from Oracle database and I need to port some tool to Informix. > > In Oracle SQL I have a query like this: > > > SELECT > > ROWNUM, COLUMN-1, COLUMN-2, ..., COLUMN-c > > FROM > > TABLE-1, TABLE-2, ..., TABLE-t > > WHERE > > CONDITIONS > > > > where the first column ROWNUM is a system column managed by Oracle > > and it contains the ordinal number of the row retrieved by SELECT. In > > short, I use this column to enumerate the result set of the SELECT > > statement. > > How could do the same in Informix SQL? > > Each Informix table has a column called ROWID, which gives a unique number > to each row. This value should not be used as a reference to a specific > row. You should use the primary key for this purpose. > > But it can be used as you describe. > > Hope this helps, > -- > I believe the poster is asking for an ordinal number to be returned - not a ROWID. In other words: SELECT number, tabname from systables order by tabname 1 aaaaaa 2 bbbbbb 3 cccccc and SELECT number, tabname from systables order by tabname DESC 1 cccccc 2 bbbbbb 3 aaaaaa I know of no such SQL driven variable. You COULD write a stored procedure and do: LET ROWNUM=1 FOREACH SELECT tabname FROM systables order by tabname RETURN ROWNUM, tabname WITH RESUME LET ROWNUM=ROWNUM+1 END FOREACH cheers j. ________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com Currently on loan to PLD/PE ________________________________________________________________________ Outside of a dog a book is a man's best friend. Inside of a dog it's too dark to read. (Groucho Marx) ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. ________________________________________________________________________