Varchar与数字的错误
对于背景:此工作正常,没有在生产中添加'E'表。Varchar与数字的错误
我试图执行下面的查询,但我得到的错误:
Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to numeric.
奇怪的是,它执行我们的测试数据库罚款 - 没有看到任何试图转换到数字所以有点困惑。不确定什么会导致这种差异,因为我假设数据类型在测试数据库和生产数据库中是相同的 - 任何想法?
谢谢。
/* - adding po balance qty causes duplicates
- adding stock category causes duplicates
- added PO comments using MAX function to return only first comment - gets rid of duplicates*/
/* Using NEW employee table as of 11/2 */
SELECT
S.team_member_name AS [ASSIGNED EMPLOYEE], --Using new employee table - only on bus unit as of 11/2
H.po_type AS [ PO TYPE],
G.order_no AS [GPS PO #],
P.po_number AS [SAP PO NUMBER],
M.department AS [DEPARTMENT],
G.order_qty AS [GPS QTY],
SUM(P.po_ordered_quantity) AS [SAP QUANTITY],
(SUM(P.po_ordered_quantity) - G.order_qty) AS [DIFFERENCE],
G.last_conf_date_cst AS [LAST CONFIRMED DATE],
K.business_unit_desc AS [BU],
M.[description] AS [DESCRIPTION],
P.material AS [MATERIAL],
MAX(P.comment) AS [PO COMMENT],
MIN(E.date) AS [FIRST SHOWN ON RPT]
FROM
(SELECT
order_no, order_qty, order_status,
last_conf_date_cst
FROM
asagdwpdx_prod.dbo.SimoxOrder1
UNION ALL
SELECT
order_no, order_qty, order_status,
last_conf_date_cst
FROM
asagdwpdx_prod.dbo.SimoxOrder2
UNION ALL
SELECT
order_no, order_qty, order_status,
last_conf_date_cst
FROM
asagdwpdx_prod.dbo.SimoxOrder3) G
JOIN
pdx_sap_user.dbo.vw_po_header H ON G.order_no = h.ahag_number
JOIN
pdx_sap_user.dbo.vw_po_item P ON H.po_number = P.po_number
JOIN
pdx_sap_user.dbo.vw_mm_material M ON P.material = M.material
JOIN
adi_user_maintained.dbo.SCM_PO_Employee_Name S ON P.po_number = S.po_number
JOIN
pdx_sap_user.dbo.vw_kd_business_unit K ON M.business_unit_code = K.business_unit_code
JOIN
adi_user_maintained.dbo.scm_po_error_tracking E ON E.po_number = P.po_number
WHERE
M.business_segment_code NOT IN ('421', '420', '422', '424') --exclude adi golf
AND E.report_source = 'gpsvssap_qty'
GROUP BY
G.order_no, -- GROUP BY needed on aggregate function in SELECT
G.order_qty,
G.order_status,
P.po_number,
P.material,
P.del_indicator,
H.po_created_by,
M.[description],
M.department,
S.team_member_name,
K.business_unit_desc,
G.last_conf_date_cst,
H.po_type
HAVING
G.order_qty <> SUM(P.po_ordered_quantity)
AND G.order_status NOT IN ('60', '90') -- excluding GPS cancelled (90) & shipped (60) - do we need to exclude other status'?
AND P.del_indicator <> 'L'
您可能想看'MAX(P.comment)'。你找不到最大的字符串。除非你的评论是数字
不是最大的评论 - 它删除它仍然不起作用 – user3496218
错误只发生在加入“E”表后 - 最后加入及其相关字段 – user3496218
@ user3496218所以其中一个数字是nvarchar – Sami
转换发生的原因是union之间的数据类型不匹配(或者可能在连接条件中)。发生此错误是因为值无法隐式转换。你的测试数据库中显然没有这个值。在您的测试数据库中也会进行转换,但总是成功的。 – HoneyBadger
这是正确的 - 两个表中的字段PO编号不同!谢谢! – user3496218