Problem with aggregates functions in subquerys
"Abaco Informática S.A." <[email protected]> Wed, 27 Aug 2003 10:00:27 -0300
| Newsgroups | gmane.comp.db.sapdb.general |
|---|---|
| Message-ID | <004201c36c9b$4242a360$e02a46c8@internet2> |
Problem with returning value of aggregate functions in subquerys
Look at this example:
CREATE TABLE T1 (CLAVE FIXED(3), COD_1 FLOAT, VAL_1 FIXED(15,4))
//
INSERT INTO T1 VALUES (1,0.4,0.4)
//
INSERT INTO T1 VALUES (2,0.4,0.4)
//
INSERT INTO T1 VALUES (3,0.4,0.4)
//
SELECT SUM(COD_1) as suma1, SUM(VAL_1) as suma2 FROM T1
the result is (1.2, 1.2) and that is right.
but in the query:
SELECT (SELECT SUM(COD_1) FROM T1) as suma1,
(SELECT SUM(VAL_1) FROM T1) as suma2 FROM DUAL
the result is (1, 1) and that is wrong
on the other hand, in the query:
SELECT (SELECT SUM(COD_1) + .9 FROM T1) as suma1,
(SELECT SUM(VAL_1) FROM T1) as suma2 FROM DUAL
the result is 2, 1 then the problem is in returning value of subquery not in calculation itself, I think it's good!.
The functions MAX, MIN work fine but AVG (for example) has the same problem.
Please if you have any suggestion to go around this problem until the bug is fixed, please tell us.
Thank you in advance.
Ing. Alejandra Ferraggine