在写Sunlike ERP插件联查时提示
无法解决 equal to 运算中 "Compatibility_198_804_30001" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突
原因为sunlike 数据库字符(varchar)采用的排序规则是Compatibility_198_804_30001,而WMS系统采用是系统默认Chinese_PRC_CI_AS 规则。
解决办法为两边的排序规则一致即可。
SELECT M.PRD_ID,M.PRD_NAME,M.SPEC,
M.UWEIGHT,M.TZ_03,M.PRD_COLOR,
ISNULL(B.QTY,0) AS QTY,ISNULL(B.QTY_ON_ODR,0) AS QTY_ON_ODR,
ISNULL(B.QTY_ON_WAY,0) AS QTY_ON_WAY,ISNULL(B.QTY_ON_PRC,0) AS QTY_ON_PRC,
ISNULL(B.QTY_ON_ODR,0) AS QTY_ON_ODR
FROM BASE_PRDT M WITH(NOLOCK)
LEFT JOIN Verp_PRDT_TOTAL B WITH(NOLOCK) ON M.PRD_ID COLLATE Compatibility_198_804_30001=B.PRD_NO
也可以把后面左连接的语句改为:
LEFT JOIN Verp_PRDT_TOTAL B WITH(NOLOCK) ON M.PRD_ID =B.PRD_NO COLLATE Chinese_PRC_CI_AS