CAST of CHAR to DECIMAL

UDA
Enthusiast

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

1 REPLY
Junior Contributor

Re: CAST of CHAR to DECIMAL

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.