sql - Teradata sql 选择失败 [2616] 计算期间发生数字溢出

Question

我得到:

Error 2616 (Numeric overflow occurred duing computation)

当我运行下面的代码时。我开发了两个单独的查询，每个查询都运行。当我将它们放入一个查询并使用 UNION 时，我收到错误消息。并集的一侧返回 274 条记录，另一侧返回 277 条记录。当我收到此消息时，我正在使用 Teradata SQL Assistant。我们正在使用版本 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

我假设您的两个查询单独运行都可以。 UNION 运算符:

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.

我无法看到您的数据并重现它，但我猜您的 DECIMAL/NUMERIC 列与第二个 SELECT 不兼容。检查它的最简单方法是注释两个语句中除一列之外的所有列，并每次运行查询时取消注释一列。当您发现哪一列导致错误时，请使用:

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

编辑:

来自@dnoeth评论:可能您需要强制转换硬编码的 0.00 AS BANK_AMT ，它被视为 DECIMAL(3,2):

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

来自@anwaar_hell评论和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.

在 Oracle/SQL Server 等其他 RDBMS 中，数据类型被隐式转换为更广泛的类型。