Select Failed [2616] Numeric Overflow occurred during computation

Database
Enthusiast

Select Failed [2616] Numeric Overflow occurred during computation

I get the Error 2616 (Numeric overflow occurred duing computation) when I run the code below.  I developed 2 separate queries that each run.  When I put them in one query and use a UNION I get the error message.  1 side of the union returns 274 records and the other side returns 277 records.  I'm using Teradata SQL Assistant when I get this message.  We are using version 14.10.0.07. Any suggestion on how to correct his would be appreciated.  Thanks for the help....

with drvd_qry (operating_unit, grp_brn_id, ecr_dept_id, stn_id,  glt_seq) as
(select
soh.operating_unit,
s.grp_brn_id,
s.ecr_dept_id,
s.stn_id,
s.glt_seq

from stns s
inner join rfs.stn_ops_hierarchies soh on soh.stn_stn_id = s.stn_id
where substr(s.grp_brn_id, 1, 2) = 'G1'
group by soh.operating_unit, s.grp_brn_id, s.ecr_dept_id, s.stn_id, s.glt_seq),

qry_drvd (ecr_ticket_no, open_item_id) as

(select
j.ecr_ticket_no,
j.open_item_id

from
rfs.journal_entries j

where
j.business_unit ='A0141'
and j.accounting_date = cast ('23-SEP-2015' as date format 'dd-MMM-YYYY')
and j.account_gl ='109850')

select
dq.operating_unit as BU,
dq.grp_brn_id as GPBR,
dq.stn_id as STN_ID,
j.department as DEPTID,
ft.mrchnt_nbr as MERCH_NUM,
j.ecr_ticket_no as TICKET_NUM,
ft.prim_acct_frst_six_dgt_nbr as FIRST6,
ft.prim_acct_last_four_dgt_nbr as LAST4,
p.auth_nbr as AUTH_NUM,
ft.stlmt_uniq_ref_nbr as REF_NUM,
j.monetary_amount as GL_AMT,
0.00 as BANK_AMT

from
rfs.journal_entries j,
rfs.pymts p,
paymt.fin_tran ft,
drvd_qry dq

where
j.business_unit = 'A0141'
and j.accounting_date = cast ('23-SEP-2015' as date format 'dd-MMM-YYYY')
and j.account_gl in (109850)
and cast(j.open_item_id as decimal(19,0)) = p.ecr_pymt_id
and p.ram_rea_rnt_agr_nbr = j.rnt_agr_nbr
and p.fin_tran_ref_id = ft.fin_tran_ref_id
and dq.ecr_dept_id = j.department

UNION

select

b.BU,
b.GPBR,
b.STN_ID,
b.DEPTID,
b.MERCH_NUM,
qd.ecr_ticket_no as TICKET_NUM,
b.FIRST6,
b.LAST4,
b.AUTH_NUM,
b.REF_NUM,
b.GL_AMT,
b.BANK_AMT

from

(select
a.BU,
a.GPBR,
a.STN_ID,
a.DEPTID,
a.MERCH_NUM,
a.REF_NUM,
a.FIRST6,
a.LAST4,
p.auth_nbr as AUTH_NUM,
p.ecr_pymt_id,
a.GL_AMT,
a.BANK_AMT

from

(select
dq.operating_unit as BU,
dq.grp_brn_id as GPBR,
dq.stn_id as STN_ID,
dq.ecr_dept_id as DEPTID,
cast(f.merch_num as varchar(20)) as MERCH_NUM,
f.ret_ref_num as REF_NUM,
ft.prim_acct_frst_six_dgt_nbr as FIRST6,
ft.prim_acct_last_four_dgt_nbr as LAST4,
0.00 as GL_AMT,

case when f.tran_typ_cde = 1 then f.tran_amt
when f.tran_typ_cde = 4 then f.tran_amt * -1
end as BANK_AMT,

ft.fin_tran_ref_id

from paymt.fndng_recncl_dtl_rprt f,
rfs.cc_mrchnt_nbr m,
drvd_qry dq,
paymt.fin_tran ft

where f.row_stat_cde = 'A'
and cast (f.tran_proc_date as date format 'MM/DD/YYYY') ='09/23/2015'
and m.mrchnt_nbr = f.merch_num
and m.credit_card_typ = 'VI'
and dq.stn_id = m.sta_stn_id
and ft.stlmt_uniq_ref_nbr = f.ret_ref_num

group by
dq.operating_unit,
dq.grp_brn_id,
dq.stn_id,
dq.glt_seq,
dq.ecr_dept_id,
f.merch_num,
f.ret_ref_num,
ft.prim_acct_frst_six_dgt_nbr,
ft.prim_acct_last_four_dgt_nbr,
GL_AMT,
BANK_AMT,
ft.fin_tran_ref_id) a

left outer join rfs.pymts p on p.fin_tran_ref_id = a.fin_tran_ref_id) b

left outer join qry_drvd qd on cast(qd.open_item_id as decimal(19,0)) = b.ecr_pymt_id
2 REPLIES
Junior Contributor

Re: Select Failed [2616] Numeric Overflow occurred during computation

The first SELECT determines the datatypes, it's the 0.00 as BANK_AMT which results in a DECIMAL(3,2), needs to be CAST(0 AS DECIMAL(18,2)) as BANK_AMT

Enthusiast

Re: Select Failed [2616] Numeric Overflow occurred during computation

Thank you.  That worked to eliminate the error message.  However, I'm still getting too many rows with the union.  For example, I see this

Group    Branch    GL AMT    BK AMT

  ABC       135           0.00       25.00

  ABC       135         25.00         0.00

What I would like to see is 1 record

   ABC      135         25.00        25.00

Can I do this with a UNION?  Thanks for the help