带有VS2008的PL/SQL AVG函数导致“算术运算导致溢出". [英] PL/SQL AVG Function with VS2008 causing "Arithmetic operation resulted in an overflow"

问题描述

我敢肯定，我在这里忽略了一些简单的事情，但是无论如何-我正在尝试构建一个通过解码功能实现AVG的PL/SQL过程，请参见下文.我不断收到算术溢出错误，但无法弄清楚需要更改哪些内容以保持该类型的正确大小(或者即使这是必需的！)

I'm sure I'm overlooking something simple here, but anyway - I'm trying to build a PL/SQL procedure which implements AVG via a decode function, see below. I keep getting an arithmetic overflow error but can't figure out what needs changing to hold the right size for the type (or even if that's what's required!)

如果我将AVG更改为计数"，总和"或最大值"，一切都很好，所以我知道解码正常工作，但我不确定为什么AVG不能正常工作.任何指针都非常感谢.

If I change the AVG to Count, Sum or Max, all is fine, so I know the decode is working correctly, I'm just not sure why AVG isn't. Any pointers greatly appreciated.

Rgds BBz

PROCEDURE GET_DATAMEANS (
    fLOTCODE IN VARCHAR2,
    fFROMDATE IN DATE,
    fTODATE IN DATE,
    THEDATA OUT SYS_REFCURSOR) IS

    TYPE loc_array_type IS TABLE OF VARCHAR2(40);  -- array type
    sql_str VARCHAR2(10000);    -- SQL statement
    loc_array    loc_array_type;     -- array for test names

    BEGIN -- executable part starts here

        -- get the test names for the given lot code
        SELECT 
            PT_TESTNAME BULK COLLECT INTO loc_array
        FROM 
            (SELECT DISTINCT
                TESTPARMS.PT_TESTNAME, TESTPARMS.PT_TESTNUM
                FROM "PRETEST".PRETEST_LOT@PRS_DBLINK LOT,
                "PRETEST".PRETEST_MEASURE@PRS_DBLINK MEASURE,
                "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK TESTPARMS
                WHERE (LOT.PT_LOTSQ = MEASURE.PT_LOTSQ)
                AND (MEASURE.PT_LOTSQ = TESTPARMS.PT_LOTSQ)
                AND (MEASURE.PT_TESTNUM = TESTPARMS.PT_TESTNUM)
                AND (LOT.PT_LOTID = fLOTCODE)
                ORDER BY PT_TESTNUM);

        -- build the SQL string
        sql_str := '';
        sql_str := sql_str ||   'SELECT ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOTID, ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOCTYPE, ' ;
        sql_str := sql_str ||   '   PRETEST_LOT.PT_TESTDATE, ';

        -- add the decodes for column headings
        FOR i IN loc_array.first..loc_array.last LOOP
            sql_str := sql_str 
                || '  AVG ( decode ( PRETEST_TEST_PARMS.PT_TESTNAME, '''
                || loc_array(i) || ''', PRETEST_MEASURE.PT_MEAS_VALUE  , null )) '
                || loc_array(i);
                IF (i < loc_array.last) THEN
                    sql_str := sql_str || ', ';
                END IF;
        END LOOP;

        -- build the remainder of the SQL
        sql_str := sql_str || ' FROM ';
        sql_str := sql_str || '     "PRETEST".PRETEST_LOT@PRS_DBLINK PRETEST_LOT, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_MEASURE@PRS_DBLINK PRETEST_MEASURE, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK PRETEST_TEST_PARMS ';

        sql_str := sql_str || ' WHERE  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTSQ = PRETEST_MEASURE.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_LOTSQ = PRETEST_TEST_PARMS.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_TESTNUM = PRETEST_TEST_PARMS.PT_TESTNUM AND ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOCTYPE=''9A08-55/T'' AND  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE Between :fFROMDATE And :fTODATE  ';

        sql_str := sql_str || ' GROUP BY ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTID,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOCTYPE,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE  ';

        sql_str := sql_str || ' ORDER BY  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE  ';

        -- run the query
        OPEN THEDATA FOR sql_str USING fFROMDATE, fTODATE;

END GET_DATAMEANS;

推荐答案

该错误似乎是由于Oracle和VS2008之间的小数位分辨率不同引起的.显然，Oracle将返回(不确定)大约36 dp，而VS2008仅返回大约27 dp(同样，不确定确切的数字).

The error appears to be caused by a difference in decimal place resolution between Oracle and VS2008. Apparently, Oracle will return around (not sure exactly) 36 dp whereas VS2008 only handles around 27 dp (again, not sure of exact number.)

用Round(....，12)包装AVG语句将返回VS2008接受的12 dp数字.

Wrapping the AVG statement with Round( .... , 12 ) will return a 12 dp number which VS2008 will accept.

这篇关于带有VS2008的PL/SQL AVG函数导致“算术运算导致溢出".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！