表达式类型numeric对于collate无效

Question

我正忙着在不同的数据库之间使用SQL中的UNION ALL。

SELECT     CostValue AS [COST OF SALES], Customer AS [CUSTOMER CODE], DocumentType AS [DOCUMENT TYPE], Invoice AS [INVOICE NO], InvoiceDate AS [INVOICE DATE], 
                  DATEPART(dd, InvoiceDate) AS [INVOICE DAY], Mass AS MASS, NetSalesValue AS SALES, NetSalesValue - CostValue AS [GROSS PROFIT], OrderType, 
                  QtyInvoiced AS QUANTITY, SalesOrder, StockCode AS [STOCK CODE], TrnMonth AS [FIN MONTH], TrnYear AS [FIN YEAR], TrnYear * 100 + TrnMonth AS YYYYMM, 
                  'SHP' AS COMPANY
FROM         SomeCompanyA.dbo.SalesDetail
WHERE     (LineType = 1) AND (TrnYear >= 2010)
UNION ALL
SELECT     CostValue AS [COST OF SALES], Customer AS [CUSTOMER CODE],     DocumentType AS [DOCUMENT TYPE], Invoice AS [INVOICE NO], InvoiceDate AS [INVOICE DATE], 
                  DATEPART(dd, InvoiceDate) AS [INVOICE DAY], Mass AS MASS, NetSalesValue AS SALES, NetSalesValue - CostValue AS [GROSS PROFIT], OrderType, 
                  QtyInvoiced AS QUANTITY, SalesOrder, StockCode AS [STOCK CODE], TrnMonth AS [FIN MONTH], TrnYear COLLATE DATABASE_DEFAULT AS [FIN YEAR], (TrnYear * 100) COLLATE database_default + TrnMonth AS YYYYMM, 
                  'SGF' AS COMPANY
FROM         SQLXXXXXX.SomeCompanyB.dbo.SalesDetail AS SalesDetail_1
WHERE     (LineType = 1) AND (TrnYear >= 2010)

我在执行“表达式类型数字对于COLLATE子句无效”时收到以下错误，我认为它来自于这个(TrnYear * 100) COLLATE database_default + TrnMonth AS YYYYMM

我不确定如何整理它，因为我认为这是一个连接，而不是一个隐式转换。

请有人就如何正确地投射这个问题提供一些建议。

我也看了一下这里的Use Collate in CONCAT，但我看不出这是如何工作的，因为我在一年中使用了运算符。

也许我漏掉了什么。任何建议都将不胜感激。

谢谢

Answer 1

COLLATE用于文本类型，(TrnYear * 100)计算结果为数字。在使用COLLATE之前，尝试使用CAST((TrnYear * 100) AS VARCHAR)转换为varchar。