Problem with select first into temp tables
Clive Eisen reported that on IDS 10.00.UC3R1TL a "SELECT FIRST 1 firstname FROM people INTO TEMP z1 WITH NO LOG" ignored the FIRST clause and loaded all 239,567 rows into the temp table, while the same SELECT without INTO TEMP correctly returned one row. Replies: one poster could not reproduce it on 10.00.FC4X4; another thought FIRST wasn't allowed with SELECT INTO at all (though no syntax error is raised); another noted the release level is old and suggested moving to a later fixpack (UC6), warning UC5 has its own SELECT/OPTCOMPIND bug. No confirmed cause or fix is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is this a (know) bug or am I being dim?
IBM Informix Dynamic Server Version 10.00.UC3R1TL
----------------------------
echo "select count(*) from people" | dbaccess data
(count(*))
239567
1 row(s) retrieved.
----------------------------
echo "select first 1 firstname from people " | dbaccess data
firstname
CHRISTINE
1 row(s) retrieved.
------------------------------
echo "select first 1 firstname from people into temp z1 with no log" |
dbaccess data
239567 row(s) retrieved into temp table.
--
Clive
A very similar statement worked for me on IBM Informix Dynamic Server
Version 10.00.FC4X4.
select first 1 first_name from employee into temp z1 with no log;
So it might be too early for me to spot the problem or it might be a
bug.
Clive Eisen wrote:
> Is this a (know) bug or am I being dim?
>
> IBM Informix Dynamic Server Version 10.00.UC3R1TL
>
> ----------------------------
> echo "select count(*) from people" | dbaccess data
>
> (count(*))
>
> 239567
>
> 1 row(s) retrieved.
>
>
> ----------------------------
> echo "select first 1 firstname from people " | dbaccess data
>
> firstname
>
> CHRISTINE
>
> 1 row(s) retrieved.
>
>
> ------------------------------
> echo "select first 1 firstname from people into temp z1 with no log" |
> dbaccess data
>
> 239567 row(s) retrieved into temp table.
>
> --
> Clive
Try unloading the records to see what is in them? Could it be a parsing
issue? Do you have a field called first in the table?
Clive Eisen wrote:
> Is this a (know) bug or am I being dim?
>
> IBM Informix Dynamic Server Version 10.00.UC3R1TL
>
> ----------------------------
> echo "select count(*) from people" | dbaccess data
>
> (count(*))
>
> 239567
>
> 1 row(s) retrieved.
>
>
> ----------------------------
> echo "select first 1 firstname from people " | dbaccess data
>
> firstname
>
> CHRISTINE
>
> 1 row(s) retrieved.
>
>
> ------------------------------
> echo "select first 1 firstname from people into temp z1 with no log" |
> dbaccess data
>
> 239567 row(s) retrieved into temp table.
>
> --
> Clive
It was my understanding that the first qualifier could not be used to select into tables, temp or not.
________________________________
From: informix-list-bounces@iiug.org on behalf of Clive Eisen
Sent: Wed 1/17/2007 6:47 AM
To: informix-list@iiug.org
Subject: Problem with select first into temp tables
Is this a (know) bug or am I being dim?
IBM Informix Dynamic Server Version 10.00.UC3R1TL
----------------------------
echo "select count(*) from people" | dbaccess data
(count(*))
239567
1 row(s) retrieved.
----------------------------
echo "select first 1 firstname from people " | dbaccess data
firstname
CHRISTINE
1 row(s) retrieved.
------------------------------
echo "select first 1 firstname from people into temp z1 with no log" |
dbaccess data
239567 row(s) retrieved into temp table.
--
Clive
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Goldrick, Jim wrote: > It was my understanding that the first qualifier could not be used to select into tables, temp or not. You may be right, although I read it as you can't use first in sub-selects - and if you try to you get a syntax error This gives no syntax error, just the wrong result -- Clive
Well it is a strange release version and you are two version behind, the
latest is UC6. UC5 appears to have a nasty SELECT/OPTCOMPIND Bug
Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com
Failure is not as frightening as regret.
Attend IDUG 2007 San Jose, North America
May 6-10, 2007
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: Clive Eisen [mailto:clive@serendipita.com]
> Posted At: 17 January 2007 06:48
> Posted To: comp.databases.informix
> Conversation: Problem with select first into temp tables
> Subject: Problem with select first into temp tables
>
>
> Is this a (know) bug or am I being dim?
>
> IBM Informix Dynamic Server Version 10.00.UC3R1TL
>
> ----------------------------
> echo "select count(*) from people" | dbaccess data
>
> (count(*))
>
> 239567
>
> 1 row(s) retrieved.
>
>
> ----------------------------
> echo "select first 1 firstname from people " | dbaccess data
>
> firstname
>
> CHRISTINE
>
> 1 row(s) retrieved.
>
>
> ------------------------------
> echo "select first 1 firstname from people into temp z1 with
> no log" | dbaccess data
>
> 239567 row(s) retrieved into temp table.
>
> --
> Clive