title: Teradata sql Select Failed [2616] Numeric Overflow occurred during computation
body: <p>I get:</p>
  
  <blockquote>
    <p>Error 2616 (Numeric overflow occurred duing computation)</p>
  </blockquote>
  
  <p>when I run the code below.  I developed 2 separate queries that each run.  When I put them in one query and use a <code>UNION</code> I get the error message.  1 side of the union returns 274 records and the other side returns 277 records.  I'm using <code>Teradata SQL Assistant</code> when I get this message.  We are using version <code>14.10.0.07</code>.</p>
  
  <pre><code>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
  </code></pre>