Art S. Kagel
06-24-2003, 11:07 AM
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@BRAGG.ARMY.MIL>
At: 6/24 13:55
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
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@BRAGG.ARMY.MIL>
At: 6/24 13:55
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