1

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!)

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;
Bob
  • 327
  • 4
  • 13
  • You've got two things going on here, and it's not immediately obvious (to me at least) which part is raising the arithmetic overflow error. (1) please remove the `OPEN THEDATA FOR sqlstr` bit and just print out the SQL that is being generated. Then, run that SQL separately and show us (a) the SQL that was being run, and (b) the actual output/error message. Thanks :) – Jeffrey Kemp Nov 19 '10 at 00:18
  • Hi, thanks for the reply. The actual error was - "Arithmetic operation resulted in an overflow." I think I've now fixed it by wrapping the AVG statement with ROUND ( ..., 12 ). Apparently, (and I realise I didn't mention I was using VS2008), Oracle will return upto 36 (?) decimal places, but VS2008 can only use 27 (?) dp. rounding to 12 decimal places has cured the problem. Thanks for the help. – Bob Nov 19 '10 at 08:36
  • well done with that. I recommend you add your findings as an answer and accept it - and tag this question with VS2008 because it's specific to that context, I think. – Jeffrey Kemp Nov 20 '10 at 08:34

1 Answers1

2

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

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

Bob
  • 327
  • 4
  • 13