All Forums UDA
YIAPIPI 9 posts Joined 02/14
21 Jan 2015
CAST of CHAR to DECIMAL

hi All,
i'm experiencing  a trouble executing the following cast code in TERADATA 14....
select
-- case
-- when GRR_SU_BMRC <> GRR_SU_Anagr then 'OK'
-- else 'KO'
-- end GRR_FLAG
DT_RFRM as Data_BMRC
,
COD_MDLL,
COUNT_RTN_SPEC,
GRR_SU_BMRC,
GRR_SU_Anagr,
CD_NDG_CLI,
A4_CORP_NAME,
tot_Auth,
current_date as DATA_ODIERNA,
DT_INIZIO_VAL,
DT_FINE_VAL,
DATA_ODIERNA - DT_INIZIO_VAL as Vetusta_GRR
order by COD_MDLL, DT_INIZIO_VAL desc, CD_NDG_CLI
from
(
SELECT
*
FROM
(
select
DT_RFRM,
COD_MDLL,
COUNT_RTN_SPEC,
A48_GRR_SNR_UNSCRD as GRR_SU_BMRC,
A4_CORP_NAME,
A36_COUNT_RTN_DT,
DT_COUNT_RTN_END
-- , A40_CLNT_LCL_ID
, cast(A40_CLNT_LCL_ID AS decimal(18)) AS ID_CLNT
FROM FEED_BMRC.CAPRI_CLIENT
where DT_RFRM='2014-12-31'
--- and COUNT_RTN_SPEC NOT IN ('11','12') non dovrebbe servire in quanto non hanno GRR_Su
AND (COD_MDLL in
(
'CA01',
'CA02',
'CA03',
'FI01') or
substr (COD_MDLL,1,1)='X' or
substr (COD_MDLL,1,1)='Y' )
)
XX
right join
-- left join
(
SELECT a.CD_SMA_TPO ,
cast(a.CD_STO as decimal(5,4)) as GRR_SU_Anagr     <-------------------------------------------
,
b.CD_NDG_CLI,
a.DT_INIZIO_VAL,
a.DT_FINE_VAL,
current_date as DATA_ODIERNA,
a.DT_INIZIO_VAL-DATA_ODIERNA as xxdate
FROM DXDWH_STG.TBCPASTOT a,
DXDWH_STG.TBCODSTR b
where a.id_cpa=b.id_cpa
--and CD_NDG_CLI='6272146'
and CD_SMA_TPO='grr'
and DATA_ODIERNA - a.DT_INIZIO_VAL <= '15'
QUALIFY ROW_NUMBER() OVER(PARTITION BY CD_NDG_CLI ORDER BY DT_INIZIO_VAL DESC) = 1
)
YY
on YY.CD_NDG_CLI=XX.ID_CLNT
where XX.ID_CLNT is not null ) ZZ
left join
(
select NDG,
CD_BNC,
current_date as oggi,
sum(AM_FD) AS tot_Auth
from DXDWH_DB.DWH_FD
WHERE dt_nz_vldt_fd <=oggi and dt_fn_vldt_fd > oggi
group by 1,2,3 ) F
ON F.NDG=ZZ.CD_NDG_CLI
 
at the end i got :     2616  "Numeric overflow occourred during computation"
Thank's for your precious support
K Regards
Pio

dnoeth 4628 posts Joined 11/04
21 Jan 2015

Hi Pio,
the error message indicates that at least one a.CD_STO doesn't fit in a decimal(5,4), so simply increase the number of digits.

Dieter

You must sign in to leave a comment.