Hello Sean,

below is a small script which reproduces the problem. As long as I
know (maybe my knowledge is just too little), both views should have 1
for SumC, but view CValueWrong provides 2. If there are 3 rows in
Table B, SumC becomes 3, and so on. To sum up, SumC is calculated as
number of Rows in TableB * C1.

Is there really a bug, or is my sql clause incorrect?

Thank,
Frank

------------------------------------------------------

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'LOCALHOST:C:\SampleDB.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET ISO8859_1;

CREATE DOMAIN D_ID AS INTEGER;
CREATE DOMAIN D_Integer AS INTEGER;
CREATE DOMAIN D_Currency AS DOUBLE PRECISION;
CREATE DOMAIN D_String100 AS VARCHAR(100) CHARACTER SET ISO8859_1
COLLATE DE_DE;

CREATE TABLE TableA (
ID D_ID NOT NULL,
A1 D_String100 NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE TableB (
ID D_ID NOT NULL,
AID D_ID NOT NULL,
B1 D_Currency,
B2 D_Integer,
PRIMARY KEY(ID)
);

CREATE TABLE TableC (
ID D_ID NOT NULL,
AID D_ID NOT NULL,
C1 D_Currency DEFAULT 0,
PRIMARY KEY(ID)
);

CREATE VIEW CValuesWrong (
AID,
A1,
SumB,
SumC
) AS
SELECT
a.ID, a.A1,
SUM(b.B1 * b.B2) AS SumB,
SUM(c.C1) AS SumC
FROM
TableA a
LEFT JOIN TableB b ON b.AID=a.ID
LEFT JOIN TableC c ON c.AID=a.ID
GROUP BY
a.ID, a.A1;

CREATE VIEW CValuesCorrect (
AID,
A1,
SumC
) AS
SELECT
a.ID, a.A1,
SUM(c.C1) AS SumC
FROM
TableA a
LEFT JOIN TableC c ON c.AID=a.ID
GROUP BY
a.ID, a.A1;

COMMIT;

INSERT INTO TableA VALUES(1, 'Sample');
INSERT INTO TableB VALUES(1, 1, 1, '1.0');
INSERT INTO TableB VALUES(2, 1, 1, '1.0');
INSERT INTO TableC VALUES(1, 1, '1.0');


COMMIT;

SELECT * FROM CValuesWrong;
SELECT * FROM CValuesCorrect;