"Arithmetic overflow error converting expression to data type nvarchar."

Question

I'm trying to do a comparison of data between 2 tables, where I need to join multiple columns as a composite key to get a unique identifier. I'm using a CTE and the code I have is:

WITH SuburbDataTest AS (
  SELECT *
    ,  CAST(Address AS NVARCHAR(100))+' ' +CAST(LivingAddress AS NVARCHAR(2))
    + ' ' + CAST(StartDate AS NVARCHAR(11))+ ' ' +CAST(AddressTypeId AS NVARCHAR(1))
    + ' ' +CAST(SuburbId AS NVARCHAR(1))AS SuburbDataTestColumn
  FROM [mig].[ConsumerAddressMigration]
  WHERE SuburbId is NOT NULL
)
SELECT *
FROM SuburbDataTest staging
WHERE SuburbDataTestColumn IN (
  SELECT Address+' ' +CAST(LivingAddress AS NVARCHAR(2))+ ' '+CAST(StartDate AS NVARCHAR(11))
    + ' ' +CAST(AddressTypeId AS NVARCHAR(1))+ ' ' +CAST(SuburbId AS NVARCHAR(1)) AS SuburbDataTestColumn
  FROM [dbo].[tblConsumerAddress]
)

Unfortunately I'm getting

Arithmetic overflow error converting expression to data type nvarchar.

Any ideas?

can we see the schema of [mig].[ConsumerAddressMigration] and that of [dbo].[tblConsumerAddress]? — zip
– zip, Commented Feb 21, 2020 at 0:47

Gordon Linoff · Accepted Answer · 2020-02-21 00:55:34Z

3

This happens when you are converting a number to a string -- and the string is not big enough. I would guess this is the problem:

CAST(SuburbId AS NVARCHAR(1))

If SuburbId is a number larger than 9, then this will generate an error. Or, for that matter if the value is negative you'll get the same error as well.

answered Feb 21, 2020 at 0:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mo_Dlg Over a year ago

Duh! I was so blind! Really grateful @Gordon Linoff!

Collectives™ on Stack Overflow

"Arithmetic overflow error converting expression to data type nvarchar."

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related