Tam Tam - 19 days ago 6
SQL Question

Handle precision difference in Oracle

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 this error:


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?

Answer

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