Hi,
I'm completely stuck on the following:

I run a select query to fill an EMTPY table with (summed) data.

Then, however, the total sum of all records doesn't match the
total sum of the direct query on the original table.

Both tables have the same layout.

How can the total sum of SUMMED/GOUPED BY data be different from the
total sum of the original data with the EXACT same select statement??

I use Oracle 10g.
These are my queries:

//empty destination table
TRUNCATE TABLE AGGPNLVECTOR

//fill table with aggregated numbers
INSERT INTO AGGPNLVECTOR
SELECT 'AGG1680', MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
ELEMENTNUMBER ,SUM(NEWVALUE),ERRORSTATUS, RISKFACTORGROUPID,
NEWIGNOREREPORTSTATUS, SUM(ALTVALUE)

FROM PNLVECTOR
WHERE MRTKCOBDATE='1-MAY-06'
AND SCENARIOID=0
AND ERRORSTATUS='V'
AND NEWIGNOREREPORTSTATUS='R'
AND (
PNLVECTOR.DESKLONGID='DSK1116'
OR PNLVECTOR.DESKLONGID='DSK1120'
OR PNLVECTOR.DESKLONGID='DSK1644'
)

GROUP BY MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
ELEMENTNUMBER ,ERRORSTATUS, RISKFACTORGROUPID,
NEWIGNOREREPORTSTATUS


//total sum no.1
SELECT SUM(NEWVALUE)
FROM PNLVECTOR
WHERE MRTKCOBDATE='1-MAY-06'
AND SCENARIOID=0
AND ERRORSTATUS='V'
AND NEWIGNOREREPORTSTATUS='R'
AND (
PNLVECTOR.DESKLONGID='DSK1116'
OR PNLVECTOR.DESKLONGID='DSK1120'
OR PNLVECTOR.DESKLONGID='DSK1644'
)

//total sum no. 2
SELECT SUM(NEW VALUE)
FROM AGGPNLVECTOR


Many thanks!
Martijn

Blaenzo wrote:
> Hi,
> I'm completely stuck on the following:
>
> I run a select query to fill an EMTPY table with (summed) data.
>
> Then, however, the total sum of all records doesn't match the
> total sum of the direct query on the original table.
>
> Both tables have the same layout.
>
> How can the total sum of SUMMED/GOUPED BY data be different from the
> total sum of the original data with the EXACT same select statement??
>
> I use Oracle 10g.
> These are my queries:
>
> //empty destination table
> TRUNCATE TABLE AGGPNLVECTOR
>
> //fill table with aggregated numbers
> INSERT INTO AGGPNLVECTOR
> SELECT 'AGG1680', MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> ELEMENTNUMBER ,SUM(NEWVALUE),ERRORSTATUS, RISKFACTORGROUPID,
> NEWIGNOREREPORTSTATUS, SUM(ALTVALUE)
>
> FROM PNLVECTOR
> WHERE MRTKCOBDATE='1-MAY-06'
> AND SCENARIOID=0
> AND ERRORSTATUS='V'
> AND NEWIGNOREREPORTSTATUS='R'
> AND (
> PNLVECTOR.DESKLONGID='DSK1116'
> OR PNLVECTOR.DESKLONGID='DSK1120'
> OR PNLVECTOR.DESKLONGID='DSK1644'
> )
>
> GROUP BY MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> ELEMENTNUMBER ,ERRORSTATUS, RISKFACTORGROUPID,
> NEWIGNOREREPORTSTATUS
>
>
> //total sum no.1
> SELECT SUM(NEWVALUE)
> FROM PNLVECTOR
> WHERE MRTKCOBDATE='1-MAY-06'
> AND SCENARIOID=0
> AND ERRORSTATUS='V'
> AND NEWIGNOREREPORTSTATUS='R'
> AND (
> PNLVECTOR.DESKLONGID='DSK1116'
> OR PNLVECTOR.DESKLONGID='DSK1120'
> OR PNLVECTOR.DESKLONGID='DSK1644'
> )
>
> //total sum no. 2
> SELECT SUM(NEW VALUE)
> FROM AGGPNLVECTOR
>
>
> Many thanks!
> Martijn

Because some other session changed the table?
In Oracle SELECTs aren't repeatable unless you set the isolation_level
of your session to serializable.
The performance drawbacks of such an approach are evident.
You could of course LOCK the table in exclusive mode.

--
Sybrand Bakker
Senior Oracle DBA

Hi Sybrand,

The database runs on a virtual machine (vmware) and I'm the only user
on the machine/database.

Are there any issues known with running 10g on a virtual machine?

Is it possible that things may be corrupt or so?

Thanks,
Martijn

sybrandb@yahoo.com wrote:
> Blaenzo wrote:
> > Hi,
> > I'm completely stuck on the following:
> >
> > I run a select query to fill an EMTPY table with (summed) data.
> >
> > Then, however, the total sum of all records doesn't match the
> > total sum of the direct query on the original table.
> >
> > Both tables have the same layout.
> >
> > How can the total sum of SUMMED/GOUPED BY data be different from the
> > total sum of the original data with the EXACT same select statement??
> >
> > I use Oracle 10g.
> > These are my queries:
> >
> > //empty destination table
> > TRUNCATE TABLE AGGPNLVECTOR
> >
> > //fill table with aggregated numbers
> > INSERT INTO AGGPNLVECTOR
> > SELECT 'AGG1680', MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> > ELEMENTNUMBER ,SUM(NEWVALUE),ERRORSTATUS, RISKFACTORGROUPID,
> > NEWIGNOREREPORTSTATUS, SUM(ALTVALUE)
> >
> > FROM PNLVECTOR
> > WHERE MRTKCOBDATE='1-MAY-06'
> > AND SCENARIOID=0
> > AND ERRORSTATUS='V'
> > AND NEWIGNOREREPORTSTATUS='R'
> > AND (
> > PNLVECTOR.DESKLONGID='DSK1116'
> > OR PNLVECTOR.DESKLONGID='DSK1120'
> > OR PNLVECTOR.DESKLONGID='DSK1644'
> > )
> >
> > GROUP BY MRTKCOBDATE, PNLVECTORTYPEID, SCENARIOID,
> > ELEMENTNUMBER ,ERRORSTATUS, RISKFACTORGROUPID,
> > NEWIGNOREREPORTSTATUS
> >
> >
> > //total sum no.1
> > SELECT SUM(NEWVALUE)
> > FROM PNLVECTOR
> > WHERE MRTKCOBDATE='1-MAY-06'
> > AND SCENARIOID=0
> > AND ERRORSTATUS='V'
> > AND NEWIGNOREREPORTSTATUS='R'
> > AND (
> > PNLVECTOR.DESKLONGID='DSK1116'
> > OR PNLVECTOR.DESKLONGID='DSK1120'
> > OR PNLVECTOR.DESKLONGID='DSK1644'
> > )
> >
> > //total sum no. 2
> > SELECT SUM(NEW VALUE)
> > FROM AGGPNLVECTOR
> >
> >
> > Many thanks!
> > Martijn
>
> Because some other session changed the table?
> In Oracle SELECTs aren't repeatable unless you set the isolation_level
> of your session to serializable.
> The performance drawbacks of such an approach are evident.
> You could of course LOCK the table in exclusive mode.
>
> --
> Sybrand Bakker
> Senior Oracle DBA