vendredi 27 février 2015

Imported a new month of data, now getting Numeric data overflow error when running query


We have an ETL job that brings in monthly revenue, and I've never experienced any issues with the data in terms of not being able to return results. The ETL job succeeded, but when I run a query to sum up total revenue for last month, I get this error:



Numeric data overflow (result precision)


We are running Amazon Redshift. I've never seen this error, and it happens whether I try to sum up total revenue, or get a distinct list of territories, for instance (without trying to return numerical data). The revenue data is stored as NUMERIC(38,7). I don't have permissions to query the raw source tables since they reside in another team's data warehouse, but I'm wondering what could be happening since the load into our database worked just fine.


Sample query I'm trying to run:



SELECT SUM(total_revenue)
FROM monthly_revenue
WHERE date_trunc('month',revenue_reporting_date) = '01-JAN-2015'


This is the full error it spits out (from SQL Workbench):



ERROR: Numeric data overflow (result precision)
Detail:
-----------------------------------------------
error: Numeric data overflow (result precision)
code: 1058
context: 64 bit overflow
query: [redacted]
location: numeric.hpp:149
process: query1_192 [pid=18716]
-----------------------------------------------

Location: File: /home/awsrsqa/padb/src/sys/xen_execute.cpp, Routine: pg_throw, Line: 5696
Server SQLState: XX000

Execution time: 6.58s

1 statement failed.


I've tried running this via SQL Workbench and Aginity, as well as within our own ETL tool. All are giving the same error. Any ideas?





Aucun commentaire:

Enregistrer un commentaire