表达式类型数字对于排序无效

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

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

请有人提供一些建议，如何正确地投射这个

我也看了一下在CONCAT中使用Collate，但我看不出这是如何工作的，因为我在年份上使用了一个操作符。

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

谢谢

Answer 1

COLLATE用于文本类型，(TrnYear * 100)计算结果为数字。在使用COLLATE.