1

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.

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.

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

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!

S3S
  • 24,809
  • 5
  • 26
  • 45
Chung Will
  • 21
  • 5

1 Answers1

1

Collation is a setting that determines how a DB should treat character data at either the server, database, or column level. There's a really good blog on this at red-gate.. Each server, and database, will have a collation. It's common for the databases and server to match, since by default a database will inherit this setting from the model database. It is uncommon to see column level collation, but that seems to be what you have here since all of your tables are coming from the same DATABASE.

You will need to figure out what the collation is on those columns. Dave Pinal has a good write up on this on his blog. You can also do this a few other ways. See the docs for that.

Once you have your collation, you can then collate the CONCAT. It will look something like the below. Here I just use the DATABASE_DEFUALT which would probably work in your case:

CONCAT(TransactionHeader.TicketStub COLLATE DATABASE_DEFAULT,TransactionHeader.TransactionNumber COLLATE DATABASE_DEFAULT) AS [DealRef]

You can find more examples of COLLATE WITH CONCAT in this answer and this one

S3S
  • 24,809
  • 5
  • 26
  • 45
  • Hi, thank you so much for your reply! I've looked through some of the blogs and questions you posted and they have definitely helped me to understand how COLLATE works a lot more. I tried your fix and I'm still getting an error "Expression type smallint is invalid for COLLATE clause" which I believe is from the TransactionNumber column. I've tried to use CAST to change it to an nvarchar but it doesn't seem to work. Again, thanks for your help! – Chung Will Jan 17 '19 at 14:59