Re: SQL - getting rowid into a temp table [368]

#1

The problem you are having is twofold, first you cannot use a column alias in a
GROUP BY clause you would have to use the ordinal position of the columns (ie
GROUP BY 1, 2, 3,...). The second and larger problem is that you cannot use a
GROUP BY clause or ORDER BY clause if you include an INTO TEMP clause. So,
fetch the raw data to the temp table then do the GROUP BY on the temp table to
calculate whatever aggregate(s) you need.

Art S. Kagel
----- Original Message -----
From: R.... Konikoff <konikoffr (AT) BRAGG (DOT) ARMY.MIL>
At: 6/24 13:55

Quote:

Simple sql question:

How do you get the rowid of a record to pass to a temp table in ISQL? I
tried renaming the field in the temp table and passing it in the clear, but
neither work. I understand that rowid is a transient value and that you
can't use the field name rowid in the temp table (since it would have it's
own)... so what can I try?

Here is what I've tried before:
=======
select rowid id_num, f1, f2, ...
from table
group by id_num, f1, f2, ...
into temp tmp_table;
=======
select rowid id_num, f1, f2, ...
from table
group by rowid, f1, f2, ...
into temp tmp_table;
=======
select rowid id_num, f1, f2, ...
from table
group by f1, f2, ...
into temp tmp_table;
===========

Thanks... Rob

sending to informix-list

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: SQL - getting rowid into a temp table [368]

comp.databases.informix comp.databases.informix

Re: SQL - getting rowid into a temp table [368] - 06-24-2003 , 03:07 PM