title: Using CAST, CONCAT and COLLATE with a LEFT OUTER JOIN
body: <p>I'm trying to CONCAT two columns and also use CAST and COLLATE but keep getting a host of different errors when I try to fix them in a way I think would work.</p>
  
  <p>Basically I am trying to CONCAT two columns together but I get a collation conflict. So then, I try and COLLATE the two columns and I then get a datatype is invalid for COLLATE error. After this I try to CAST the column giving me the error to change it to a varchar but it doesn't work. I'm just unsure how to make all 3 work together.</p>
  
  <pre><code>SELECT TransactionHeader.TransactionType,
  TransactionHeader.TicketStub,
  CAST ( TransactionHeader.TransactionNumber AS nvarchar(8)) AS [TN],
  TransactionHeader.ActualAmount,
  Currencies.SwiftCode,
  TransactionHeader.CurrencyID,
  Divisions.ShortName,
  DealHeader.StartDateNumber,
  DealHeader.EndDateNumber,
  
  CONCAT (TransactionHeader.TicketStub,                 
  TransactionHeader.TransactionNumber) AS [DealRef]
  
  FROM   Company.dbo.TransactionHeader TransactionHeader
  LEFT OUTER JOIN Company.dbo.DealHeader DealHeader
      ON TransactionHeader.THDealID=DealHeader.DHDealID
  LEFT OUTER JOIN Company.dbo.Currencies Currencies
      ON TransactionHeader.CurrencyID=Currencies.CRRecordID
  LEFT OUTER JOIN Company.dbo.Divisions Divisions
      ON TransactionHeader.PrimaryPartyID=Divisions.DVRecordID
  
  WHERE TransactionHeader.TicketStub COLLATE DATABASE_DEFAULT
  = TransactionHeader.TransactionNumber COLLATE DATABASE_DEFAULT
  </code></pre>
  
  <p>All in all, I just want to CONCAT the TicketStub and TransactionNumber Columns but I am not sure how to get past the errors I'm getting. As far as the COLLATE goes I'm still kind of usnsure how it even works, I just know to fix the collation error I need to do it. I am very new to T-SQL and have only been writing it for the past month and a half so please, any advice at all would be very helpful. Thank you!</p>