Hi
I have the following query aggregating values over a column and
creating the sum over another column. Here everything works out fine.
SELECT c.timestamp, AVG(c.response) as response, SUM(c.quantity) as
quantity, c.trans
FROM
tbl_repo01_dest c
GROUP BY c.trans, c.timestamp
No I embed this query into another, more complex one to join the
results of the query with another table.
SELECT
b.timestamp,
b.response as response_cics,
a.response as response_arch,
b.quantity as quantity_cics,
a.quantity as quantity_arch,
b.trans as trans_cics,
a.trans as trans_arch
FROM
(tbl_repo06_dest) b ,
(SELECT c.timestamp, AVG(c.response) as response, SUM(c.quantity)
as quantity, c.trans from tbl_repo01_dest c group by c.trans,
c.timestamp) a
WHERE
a.trans=b.trans
AND
to_date(a.timestamp,'DD.MM.YYYY HH24:MI')=to_date
(b.timestamp,'DD.MM.YYYY HH24:MI')
The join works out fine and all the matching tuples are joined.
However, the columns constructed with the AVG and SUM Functions all
contain a value of 1 wich is not correct. I tried to use TO_NUMBER
both in the inner and outer SELECT without a result.
Any help is greatly appreciated
Bye
Marc