Handle precision difference in Oracle

Question

Hello,

I have a piece of my query in Oracle that generates discrete percentile:

...
PERCENTILE_DISC(0.9999) WITHIN GROUP(ORDER BY DURATION_COUNT) as PERCENTILE_9999_QTY,   
...

The data type of PERCENTILE_9999_QTY is Number(8)

it works fine except in some cases I get:

ORA-01438: value larger than specified precision allowed for this column

I prefer not to change the Number(8) data type. Is there a way to insure that the value fits into the Number(8) precision?

Thanks,

Tam

Answer 1

If the result cannot fit in your N(8) column then there's not much you can do except (a) raise an exception, or (b) put something else instead:

SELECT ...
   CASE
      WHEN PERCENTILE_DISC(0.9999)
           WITHIN GROUP(ORDER BY DURATION_COUNT)
           < 100000000
      THEN PERCENTILE_DISC(0.9999)
           WITHIN GROUP(ORDER BY DURATION_COUNT)
      ELSE NULL
   END as PERCENTILE_9999_QTY,
...

Answer 2

Hi Tam,

From the documentation:

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

If you want Number(8) as output you have to execute this function on a dataset that fits in NUMBER(8).