ERROR: Teradata execute: Numeric overflow occurred during computation

Database

ERROR: Teradata execute: Numeric overflow occurred during computation

I've got this error: Teradata execute: Numeric overflow occurred during computation.

I have read the similar questions on this forum but still can't understand what should I do to resolve the problem. I supposed that the reason is that I have computation on step when i devide NATIONAL_AMOUNT on DEBT_EXPENSE.

After using CAST there was no resolving still.

Has anyone thoughts? Please help me. I'm in dispair.

I think that the problem is here:

    EXECUTE (create multiset volatile table Debt_&mon. AS 
(SELECT
t1.TABLE_BUSINESS_MONTH,
t1.REGION,
t1.SEGMENT,
t1.ACC_METH,
COUNT(t1.ACC_N) as Count_ACC_N,
SUM(t1.NATIONAL_UNPAID) AS NATIONAL_UNPAID,
SUM(t1.DEBT_EXPENSE) as DEBT_EXPENSE,
SUM(t2.NATIONAL_AMOUNT) as NATIONAL_AMOUNT,
(case when SUM(t2.NATIONAL_AMOUNT)=0 then 0 else (cast(SUM(t1.DEBT_EXPENSE) as Decimal(20,6))/cast(SUM(t2.NATIONAL_AMOUNT) as Decimal(20,6)) end) as 'РСДЗ/Д'n,
(case when SUM(t1.DEBT_EXPENSE)=0 then 0 else cast(SUM(t2.NATIONAL_AMOUNT) as Decimal(20,6))/cast(SUM(t1.DEBT_EXPENSE) as Decimal(20,6)) end) as 'Д/ДЗ'n

FROM Prev_Debt_&mon. t1
LEFT JOIN CLC_&mon. t2 on
t1.REGION=t2.REGION and
t1.SEGMENT=t2.SEGMENT and
t1.ACC_METH=t2.ACC_METH
GROUP BY t1.TABLE_BUSINESS_MONTH, t1.REGION, t1.SEGMENT, t1.ACC_METH)

WITH DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;)
by teradata;

======================The full code below====================================

EXECUTE (create multiset volatile table CLC_&mon. AS 
(SELECT
t1.TABLE_BUSINESS_MONTH,
t1.REGION,
Case when t2.SEGMENT is Null then 'NEW' else t2.SEGMENT end as "SEGMENT",
t1.ACC_METH,
COUNT(t1.ACC_N) as Count_ACC_N,
SUM(t1.NATIONAL_AMOUNT) AS NATIONAL_AMOUNT,
SUM(t1.NATIONAL_AMOUNT)/COUNT(t1.ACC_N) as 'ARPaU'n

FROM UAT_V_BASE.RA_FCLC_ACC t1
LEFT JOIN prd_sbx_general.&login._LIST_MK_2 t2 ON (t1.MARKET_CATEGORY=t2.MARKET_CATEGORY)
GROUP BY t1.TABLE_BUSINESS_MONTH, t1.REGION, "SEGMENT", t1.ACC_METH
WHERE SEGMENT in ('B2C','B2B','VIP','OTHER')
AND t1.ACC_METH in (1, 2)
AND TABLE_BUSINESS_MONTH between &firstmonth. and &lastmonth.
AND MRGN_ID=&MR.)
WITH DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;)
by teradata;

create table CLC_&mon. as select
TABLE_BUSINESS_MONTH FORMAT=Date9.,
REGION FORMAT=FSRGC.,
SEGMENT FORMAT=$9.,
ACC_METH FORMAT=F78C.,
Count_ACC_N,
NATIONAL_AMOUNT FORMAT=NUMX16.2 LABEL="(Сумма начислений с учетом налогов), RUB)",
'ARPaU'n
from connection to teradata
(select distinct
TABLE_BUSINESS_MONTH,
REGION,
SEGMENT,
ACC_METH,
Count_ACC_N,
NATIONAL_AMOUNT,
'ARPaU'n
GROUP BY TABLE_BUSINESS_MONTH, REGION, SEGMENT, ACC_METH, Count_ACC_N, NATIONAL_AMOUNT,'ARPaU'n
ORDER BY TABLE_BUSINESS_MONTH, REGION, SEGMENT, ACC_METH
from CLC_&mon.);

EXECUTE (create multiset volatile table Prev_Debt_&mon. AS
(SELECT
t1.TABLE_BUSINESS_MONTH,
t1.REGION,
Case when t2.SEGMENT is Null then 'NEW' else t2.SEGMENT end as "SEGMENT",
t1.ACC_METH,
t1.BASE_AGE,
Case when t1.BASE_AGE=-1 then 'ОДЗ'
when t1.BASE_AGE=3 then 'CДЗ'
else 'Другая ДЗ'
end as "Tип ДЗ",
Case when t1.NATIONAL_UNPAID between 0 and 200000 then '0...200'
when t1.NATIONAL_UNPAID between 200000 and 500000 then '200...500'
else '> 500' end as 'Amount_of_Debt'n,
t1.ACC_N,
SUM(t1.NATIONAL_UNPAID) AS NATIONAL_UNPAID,
Case when t1.EXCEPTED_FROM_BUH eq 0 then (t1.NT_BUH_DELTA + t1.NT_WRITE_OFF)
else t1.NT_WRITE_OFF_COLLFIN
end AS DEBT_EXPENSE

FROM UAT_V_BASE.RA_FDEB_ACC t1
LEFT JOIN prd_sbx_general.&login._LIST_MK_2 t2 ON (t1.MARKET_CATEGORY=t2.MARKET_CATEGORY)
WHERE SEGMENT in ('B2C','B2B','VIP','OTHER')
AND t1.ACC_METH in (1, 2)
AND TABLE_BUSINESS_MONTH between &firstmonth. and &lastmonth.
AND MRGN_ID=&MR.
GROUP BY t1.TABLE_BUSINESS_MONTH, t1.REGION, "SEGMENT", t1.ACC_METH, t1.BASE_AGE,'Tип ДЗ'n, t1.ACC_N, 'Amount_of_Debt'n, "DEBT_EXPENSE")
WITH DATA PRIMARY INDEX (ACC_N) ON COMMIT PRESERVE ROWS;)
by teradata;

EXECUTE (create multiset volatile table Debt_&mon. AS
(SELECT
t1.TABLE_BUSINESS_MONTH,
t1.REGION,
t1.SEGMENT,
t1.ACC_METH,
COUNT(t1.ACC_N) as Count_ACC_N,
SUM(t1.NATIONAL_UNPAID) AS NATIONAL_UNPAID,
SUM(t1.DEBT_EXPENSE) as DEBT_EXPENSE,
SUM(t2.NATIONAL_AMOUNT) as NATIONAL_AMOUNT,
(case when SUM(t2.NATIONAL_AMOUNT)=0 then 0 else (cast(SUM(t1.DEBT_EXPENSE) as Decimal(20,6))/cast(SUM(t2.NATIONAL_AMOUNT) as Decimal(20,6)) end) as 'РСДЗ/Д'n,
(case when SUM(t1.DEBT_EXPENSE)=0 then 0 else cast(SUM(t2.NATIONAL_AMOUNT) as Decimal(20,6))/cast(SUM(t1.DEBT_EXPENSE) as Decimal(20,6)) end) as 'Д/ДЗ'n

FROM Prev_Debt_&mon. t1
LEFT JOIN CLC_&mon. t2 on
t1.REGION=t2.REGION and
t1.SEGMENT=t2.SEGMENT and
t1.ACC_METH=t2.ACC_METH
GROUP BY t1.TABLE_BUSINESS_MONTH, t1.REGION, t1.SEGMENT, t1.ACC_METH)

WITH DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;)
by teradata;

Create table RSDZ_&mon. as select
TABLE_BUSINESS_MONTH FORMAT=Date9.,
REGION FORMAT=FSRGC.,
SEGMENT FORMAT=$9.,
ACC_METH FORMAT=F78C.,
Count_ACC_N,
NATIONAL_UNPAID FORMAT=NUMX16.2 ,
DEBT_EXPENSE FORMAT=NUMX16.2,
NATIONAL_AMOUNT FORMAT=NUMX16.2,
'РСДЗ/Д'n FORMAT=NUMX16.10,
'Д/ДЗ'n FORMAT=NUMX16.10

from connection to teradata
(select distinct
TABLE_BUSINESS_MONTH,
REGION,
SEGMENT,
ACC_METH,
Count_ACC_N,
NATIONAL_UNPAID,
DEBT_EXPENSE,
NATIONAL_AMOUNT,
'РСДЗ/Д'n,
'Д/ДЗ'n

GROUP BY TABLE_BUSINESS_MONTH, REGION, SEGMENT, ACC_METH, Count_ACC_N, NATIONAL_UNPAID, DEBT_EXPENSE, NATIONAL_AMOUNT, 'РСДЗ/Д'n, 'Д/ДЗ'n
ORDER BY TABLE_BUSINESS_MONTH, REGION, SEGMENT, ACC_METH

from Debt_&mon.);

Tags (1)
1 REPLY

Re: ERROR: Teradata execute: Numeric overflow occurred during computation

Actualy, I found how to solve my problem. I took my code where I had trouble apart from full code. The log got the error "Numeric overflow occurred during computation." but sighned that the trouble is with column NATIONAL_AMOUNT. Then I changed my SUM(t2.NATIONAL_AMOUNT) as NATIONAL_AMOUNT, on t2.NATIONAL_AMOUNT. After that code had worked.