RE: Problem with "select count(*) into"

Chris, I had a similar problem, but using ESQL/C. The return (data) type for SELECT COUNT(*) seems to change with the structure of the Query. For thequeries I tested a straightforward SELECT COUNT(*) .. INTO ... returned an integer. But when I add a fragment to the Query the return type seems to be of type DECIMAL. So try using a DECIMAL type in the INTO clause. Chaminda Peries #include <disclaimer.h> -----Original Message----- From: Christian Allaire [mailto:c_allaire@hotmail.com] Sent: Thursday, March 18, 1999 10:35 AM To: informix-list@iiug.org Subject: Problem with "select count(*) into" Hi, I have a problem in a 4gl program running on a SE 7.1 DB. When I run the following query: select count(*) into f_count_translog from translog where doc_num = "2019909" and doc_typ = 'QUO' and tr_status = "WS4" I get the following error: FORMS statement error number -1213. Character to numeric conversion error But the same query run directly in isql (without the "into" part) works fine. The f_count_translog variable is really an integer. I tried doing a secheck on the translog table which gave me: BCHECK C-ISAM B-tree Checker version 7.10.UC1 Copyright (C) 1981-1994 Informix Software, Inc. Software Serial Number AAA#J967194 C-ISAM File: translo376 Checking dictionary and file sizes. Index file node size = 1024 Current C-ISAM index file node size = 1024 Checking data file records. Checking indexes and key descriptions. Index 1 = unique key 0 index node(s) used -- 1 index b-tree level(s) used Index 2 = unique key (0,4,2) 765 index node(s) used -- 3 index b-tree level(s) used Index 3 = duplicates (29,3,0) (19,10,0) (48,3,0) 1530 index node(s) used -- 3 index b-tree level(s) used Index 4 = unique key (0,4,2) (4,8,0) (29,3,0) (19,10,0) (48,3,0) 3308 index node(s) used -- 4 index b-tree level(s) used Checking data record and index node free lists. 5605 index node(s) used, 0 free -- 59517 data record(s) used, 1 free Can someone help me with this ? Thank you, Chris Get Your Private, Free Email at http://www.hotmail.com

Hi, create procedure int (a float) returning int; define b int; let b=a; return b; end procedure; unload to "output_file_name" select int(count(*)) from table_name; The above procedure could be applied to get an integer value all the time ... while we do a select count(*) ..... PK "Peries, Chaminda" wrote: > Chris, > > I had a similar problem, but using ESQL/C. The return (data) type for > SELECT COUNT(*) seems to change with the structure of the Query. For the> queries I tested a straightforward SELECT COUNT(*) .. INTO ... returned an > integer. But when I add a fragment to the Query the return type seems to be > of type DECIMAL. So try using a DECIMAL type in the INTO clause. > > Chaminda Peries > > #include <disclaimer.h> > > -----Original Message----- > From: Christian Allaire [mailto:c_allaire@hotmail.com] > Sent: Thursday, March 18, 1999 10:35 AM > To: informix-list@iiug.org > Subject: Problem with "select count(*) into" > > Hi, > > I have a problem in a 4gl program running on a SE 7.1 DB. > When I run the following query: > > select count(*) into f_count_translog > from translog > where doc_num = "2019909" > and doc_typ = 'QUO' > and tr_status = "WS4"> > I get the following error: > > FORMS statement error number -1213. > Character to numeric conversion error > > But the same query run directly in isql (without > the "into" part) works fine. > > The f_count_translog variable is really an integer. > > I tried doing a secheck on the translog table which > gave me: > > BCHECK C-ISAM B-tree Checker version 7.10.UC1 > Copyright (C) 1981-1994 Informix Software, Inc. > Software Serial Number AAA#J967194 > > C-ISAM File: translo376 > > Checking dictionary and file sizes. > Index file node size = 1024 > Current C-ISAM index file node size = 1024 > Checking data file records. > Checking indexes and key descriptions. > Index 1 = unique key > 0 index node(s) used -- 1 index b-tree level(s) used > Index 2 = unique key (0,4,2) > 765 index node(s) used -- 3 index b-tree level(s) used > Index 3 = duplicates (29,3,0) (19,10,0) (48,3,0) > 1530 index node(s) used -- 3 index b-tree level(s) used > Index 4 = unique key (0,4,2) (4,8,0) (29,3,0) (19,10,0) (48,3,0) > 3308 index node(s) used -- 4 index b-tree level(s) used > Checking data record and index node free lists. > 5605 index node(s) used, 0 free -- 59517 data record(s) used, 1 free > > Can someone help me with this ? > > Thank you, > > Chris > Get Your Private, Free Email at http://www.hotmail.com