Icon View Incident Report

Serious Serious
Reported By: Helmut Schiefer
Reported On: 9/21/2003
For: Version 3.26 Build 1
# 1422 SQL SELECT HAVING Clause Not Finding Having Expressions in Source Columns Properly

The following query exits with the error "Expression in HAVING clause not found in source column"

DROP TABLE IF EXISTS TABLE1;

CREATE TABLE TABLE1(
  "T1_Key" INTEGER,
  "T1_SFld1" CHAR(30),
  "T1_SFld2" CHAR(30),
  "T1_Fld1" CHAR(10),
  "T1_Fld2" CHAR(10),
  PRIMARY KEY (T1_Key)
  LANGUAGE 'English (USA)'
);

CREATE INDEX ix_T1_SFld1 ON TABLE1 (T1_SFld1);

/* DBISAM creation script for table TABLE2.dat 2003-09-22 */

DROP TABLE IF EXISTS TABLE2;

CREATE TABLE TABLE2(
  "T2_Key1" INTEGER,
  "T2_Key2" INTEGER,
  "T2_SFld1" CHAR(10),
  "T2_FKey1" CHAR(10),
  "T2_Fld1" CHAR(10),
  PRIMARY KEY (T2_Key1, T2_Key2)
  LANGUAGE 'English (USA)'
);

CREATE INDEX ix_T2_Key1 ON TABLE2 (T2_Key1);
CREATE INDEX ix_T2_FKey1 ON TABLE2 (T2_FKey1);

SELECT
   a.T1_KEY,
   SUM(b.T2_KEY2) as SUMMARY
 FROM
   TABLE1 a
 INNER JOIN
   TABLE2 b ON (a.T1_KEY = b.T2_KEY1)
  WHERE
   (a.T1_KEY BETWEEN 0 AND 10000)
 GROUP BY
   a.T1_KEY
 HAVING
   (SUM(b.T2_KEY2) > 100000);



Comments Comments and Workarounds
The workaround would be to use the column alias SUMMARY instead of the source column aggregate expression.


Resolution Resolution
Fixed Problem on 10/6/2003 in version 3.27 build 1
Image