Teradata sql Select Failed [2616] Numeric Overflow occurred during computation

Question

I get:

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.

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

Answer 1

Correct this

CAST(0 AS DECIMAL(18,2))  for BANK_AMT 

Answer 2

I assume that both your query run separately are ok. The UNION operator:

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:

• The number and the order of the columns must be the same in all queries.

• The data types must be compatible.

I cannot see you data and reproduce it, but I guess you have DECIMAL/NUMERIC column that is not compatible with second SELECT. The easiest way to check it is to comment all columns in both statements except one and run your query uncommenting one column every time. When you find which column cause error use:

CAST(col_name AS type)  -- where type is broader type

EDIT:

From @dnoeth comment: Probably you need to cast hardcoded 0.00 AS BANK_AMT which is treated as DECIMAL(3,2):

CAST(0.00 AS DECIMAL(18,2)) AS BANK_AMT

From @anwaar_hell comment and Teradata UNION:

All SQL statements being combined with UNION, have to return the same number of columns, furthermore, the datatypes of all columns across the participating select statements have to match. If they don’t match, the data types of the very first SQL select statement will be the relevant one and not matching columns of the other select statements will be implicitly casted to the same data type the very first select statement has.

Keep this in mind, especially if your column is a character data type, as this could cause hidden truncation of text columns; This is a problem which is very difficult to discover.

In other RDBMS like Oracle/SQL Server the datatype is implicitly casted to broader one.