Re: different results procedure versus query analyser

From: Blaenzo <blaenzo_at_hotmail.com>
Date: 30 May 2006 12:04:52 -0700
Message-ID: <1149015892.743130.174840@j55g2000cwa.googlegroups.com>

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.CRTDATETIME));

/* 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; Received on Tue May 30 2006 - 14:04:52 CDT