Re: select and insert-select statements have different results

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