Hi,

I use a 'sum/group by' query in a stored procedure to calculate some
aggregated levels.

However, the results are slightly different from the EXACT SAME query
run by hand in a query analyser window (same user).

The procedure stores some records of a certain table1 into a temporary
table and directly uses a insert/select sum statement to add summed
values to an other table2.

Directly selecting/summing these data from table1 by hand results in
(some) different records.

The use of the temp table is for other purposes.
I stripped the code as much as posible and replaced the temp table by a
'real' table and still get different results.

I use Oracle 10g XE on Win2K.

Does this sound familiar to anyone?
Many thanks,
Martijn

This is my sproc btw:

CREATE OR REPLACE PROCEDURE "VARADMIN"."INSERTPNLVECTORFORAGGPOS2"(
VMRTKCOBDATE IN DATE,
VAGGPOSLONGID IN VARCHAR2)
AS
BEGIN
INSERT INTO TEMPTABLE
SELECT VAGGPOSLONGID, MRTKCOBDATE, PNLVECTORTYPEID,
SCENARIOID, ELEMENTNUMBER ,NEWVALUE,ERRORSTATUS,
RISKFACTORGROUPID, NEWIGNOREREPORTSTATUS, ALTVALUE
FROM PNLVECTOR
WHERE MRTKCOBDATE=VMRTKCOBDATE
AND SCENARIOID=0
AND ERRORSTATUS='V'
AND NEWIGNOREREPORTSTATUS='R'
AND DESKLONGID IN
(SELECT DESKLONGID
FROM AGGDESKMAPPING
WHERE AGGPOSLONGID=VAGGPOSLONGID
AND CRTDATETIME=VMRTKCOBDATE
AND DESKLONGID NOT IN
(SELECT DESKLONGID
FROM AGGDESKMAPPING, AGGHIERARCHY
WHERE PARENT=VAGGPOSLONGID
AND CHILD=AGGPOSLONGID
AND AGGDESKMAPPING.CRTDATETIME=AGGHIERARCHY.CRTDATETIM E));

/* INSERT INTO TEMPTABLE
SELECT *
FROM AGGPNLVECTOR
WHERE MRTKCOBDATE=VMRTKCOBDATE
AND AGGPOSLONGID IN
(SELECT DISTINCT CHILD
FROM AGGHIERARCHY
WHERE PARENT=VAGGPOSLONGID
AND CRTDATETIME=VMRTKCOBDATE); */

INSERT INTO AGGPNLVECTOR
SELECT AGGPOSLONGID, MRTKCOBDATE, PNLVECTORTYPEID,
SCENARIOID, ELEMENTNUMBER ,SUM(NEWVALUE),
ERRORSTATUS, RISKFACTORGROUPID,
NEWIGNOREREPORTSTATUS, SUM(ALTVALUE)
FROM TEMPTABLE
WHERE AGGPOSLoNGID=VAGGPOSLONGID
GROUP BY AGGPOSLONGID, MRTKCOBDATE, PNLVECTORTYPEID,
SCENARIOID, ELEMENTNUMBER ,ERRORSTATUS,
RISKFACTORGROUPID, NEWIGNOREREPORTSTATUS;

END;