Re: SQL Count
>Message-Id: <199204211959.AA06708@tbird.cc.bellcore.com>
>From: uunet!moscow.cc.bellcore.com!foxj (fox,james p)
>Date: 21 Apr 1992 15:50 EDT
>Subject: SQL Count
>X-Informix-List-Id: <list.1083>
>
> I have come across what seems like a simple problem but I can't
>think of an easy solution to it.
>
>I have a table with the following columns:
> FIELD1
> FIELD2
> FIELD3
>
>and an Ace report select of:
> SELECT UNIQUE FIELD1, FIELD2 from table;>
>Before I execute this report I would like to know how many rows
>will be selected but it seems impossible to use count as I
>cannot specify two fields on the UNIQUE. By the way I am using
>ESQL/C.
>
>I know I could do it by selecting into a "temp" table but I'm
>concerned about the performance.
(Jonathan Leffler - infmx!obelix!johnl@uunet.UU.NET writes):
>>I don't think there is a solution to this using ANSI standard SQL, nor
>>using Informix's extensions except, as you say, by using a temporary table
>>and doing the count on that. Incidentally, you could run the main report
>>query against the temporary table; you could also create an index on it if
>>that would help sufficiently.
While this will certainly work, there is a better solution if performance
is really a concern.
>>How accurately do you need to know the answer? You have two choices:
>>approximately and exactly. If it is exactly, you are stuck. ...
(ZZZZ!) Sorry Jonathan - wrong answer! Using the ESQL/C tool makes it
relatively easy. However, if the engine is ONLINE and the problem is
static (count the same columns in the same table all the time) then the
solution supplied by David Cortesi seems best. Failing that set of
conditions, the following ESQL/C code will provide a generic solution
to your problem. It requires input of the form:
<tablename>
<column name> |
<column name> |
. | names of column(s) that define uniqueness
. |
. |
where <where clause>
<more where clause>
.
.
This input may come from a file (supplied as a parameter) or from stdin.
Error checking and input parsing flexibility have intentionally been
minimized so feel free to hack the code to suit your needs.
#include <stdio.h>
#include "/usr/local/informix/incl/sqlca.h"
#define FALSE 0
#define TRUE 1
main(argc,argv)
short argc;
char *argv[];
{
FILE *fp_in;
short need_table = TRUE, need_where = TRUE;
int i, row_num = 0, num_cols = 0;
char where_clause[2048], line_in[256];
char in_file[32], colname[20], tabname[20];
char *chr_ptr, *this_line, *malloc(), *col_names[20];
$ char query[1024], buffer[2048], *dbase_name = "db_name";
if (--argc) {
strcpy(in_file,*++argv);
if (! (fp_in = fopen(in_file,"r")))
exit(fprintf(stderr,"ERROR: input file %s open failed\\n",in_file));
} else {
fp_in = stdin;
strcpy(in_file,"stdin");
}
while (fgets(line_in,255,fp_in)) {
line_in[strlen(line_in) - 1] = line_in[strlen(line_in)];
if (need_table || need_where) {
this_line = line_in;
chr_ptr = this_line = strtok(line_in," ");
while (*chr_ptr != '\\0')
*chr_ptr++ = tolower(*chr_ptr);
chr_ptr = this_line;
if (strcmp(chr_ptr,"where") == 0) {
if (need_table)
exit(fprintf(stderr,"ERROR: where clause encountered before table name.\\n"));
if (! num_cols)
exit(fprintf(stderr,"ERROR: where clause encountered before any column names.\\n"));
need_where = FALSE;
line_in[strlen(chr_ptr)] = '\\40';
strcpy(where_clause,line_in);
} else
if (need_table) {
need_table = FALSE;
strcpy(tabname,chr_ptr);
} else {
col_names[num_cols] = malloc(strlen(chr_ptr) + 1);
strcpy(col_names[num_cols++],chr_ptr);
}
} else {
strcat(where_clause," ");
strcat(where_clause,line_in);
}
}
if (need_table || num_cols == 0 || need_where)
exit(fprintf(stderr,"ERROR: input file %s did not contain the necessary information.\\n",in_file));
fclose(fp_in);
$ database $dbase_name;
if (sqlca.sqlcode)
exit(fprintf(stderr,"Error: database open failed!\\n"));
strcpy(query,"select unique ");
for (i=0; i<num_cols; i++) {
strcat(query,col_names[i]);
if (i < num_cols - 1)
strcat(query,",");
}
strcat(query," from ");
strcat(query,tabname);
strcat(query," ");
strcat(query,where_clause);
$ prepare q_id from $query;
if (sqlca.sqlcode)
exit(fprintf(stderr,"Error: query prepare failed!\\n"));
$ declare q_ptr cursor for q_id;
$ open q_ptr;
if (sqlca.sqlcode)
exit(fprintf(stderr,"Error: query open failed!\\n"));
for (;;) {
$ fetch q_ptr into $buffer;
if (sqlca.sqlcode == SQLNOTFOUND)
break;
else
if (sqlca.sqlcode)
exit(fprintf(stderr,"Error: query fetch failed!\\n"));
row_num++;
}
printf("unique count = %d\\n",row_num);
}
One final note: In the few tests that I ran comparing the ESQL/C code
against ISQL, the ESQL/C code was always significantly faster. Thanks
for the interesting problem. I hope this helps.
Ron Whiteleather
ronw@hpdmmef.boi.hp.com