1

I am busy with a UNION ALL in SQL between different databases. 

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)

I am receiving the following error upon execution "Expression type numeric is invalid for COLLATE clause" which I think is coming from this (TrnYear * 100) COLLATE database_default + TrnMonth AS YYYYMM

I am not sure how else to COLLATE this as I understand this as a concatenation and not an implicit conversion.

Please could someone offer some advice as to how to cast this correctly.

I've also taken a look here Use Collate in CONCAT but I can't see how this would work as I'm using an operator on the year.

Perhaps I'm missing something. Any advice would be appreciated.

Thanks

Community
  • 1
  • 1
Metamorphosis
  • 95
  • 1
  • 1
  • 10
  • Use instead: `CAST(TrnYear * 100 AS VARCHAR(100)) + TrnMonth AS YYYYMM` – Lukasz Szozda Oct 07 '15 at 11:35
  • @lad2025 Thanks for this. As I mentioned below to dst I am getting an arithmetic overflow error converting like this. I will search the forums for this error. Any suggestions welcome. Thanks – Metamorphosis Oct 07 '15 at 11:45

1 Answers1

5

COLLATE is for textual types, and (TrnYear * 100) evaluates to a numeric. Try converting to a varchar with CAST((TrnYear * 100) AS VARCHAR) before using the COLLATE.

davestevens
  • 2,233
  • 2
  • 16
  • 19
  • 1
    Thanks very much. From here I am receiving another error. Arithmetic overflow error converting varchar to data type numeric. SELECT TrnYear * 100 + TrnMonth AS YYYYMM FROM SomeCompanyA.dbo.SalesDetail UNION ALL SELECT CAST((TrnYear * 100) AS VARCHAR) + TrnMonth AS YYYYMM FROM SQLXXXXXX.SomeCompanyB.dbo.SalesDetail AS ArTrnDetail_1 – Metamorphosis Oct 07 '15 at 11:41
  • Re-reading the SQL, not sure why you need the COLLATE at all. Isn't (TrnYear * 100) + TrnMonth AS YYYYMM sufficient as it is in the first part of the UNION statement? – davestevens Oct 07 '15 at 11:49
  • If I do take this out and use it as it is in the first part of the statement I am getting the following Error Message "Implicit conversion of char to varchar cannot be performed because of a collation of the value is unresolved due to a collation conflict. – Metamorphosis Oct 07 '15 at 11:58
  • Difficult to see the problem from here without access to the schemas of the two tables you are querying against. Anyway I don't think your latest error is coming from the column YYYYMM. Can you try simplifying your request by removing all the other columns? – davestevens Oct 07 '15 at 13:10
  • Agreed. The errors message doesn't help isolate the issue... I did resolve the issue. Turns out it had nothing to do with YYYYMM. There was more to the script than this and it was actually a text column that needed a COLLATE. Thanks again for the input. – Metamorphosis Oct 07 '15 at 20:44