Getting the "Arithmetic overflow error converting nvarchar to data type numeric"

1

Hi,

I have a query to join a couple of tables:

image

But I'm getting this error:

image

"DNIS" is a column with digits representing phone numbers.

Could somebody please help me in resolving this error? Any help is appreciated.

Thanks,
Sajeel

2

Welcome

So you have identical data columns with 2 different data types. in those places where you might have phone numbers as varchar like 206-676-3455 and numeric 2066763455 you will get that error.

you have to decide if it should be numeric or varchar. To demonstrate

declare @dim_call_segments table(DNIS nvarchar(50) )
declare @IVRRouting table(DNIS numeric)

insert into @dim_call_segments
select '206 6666666'

insert into @IVRRouting
select '2066666666'

select *
  from @dim_call_segments s
  join @IVRRouting ivr on s.DNIS = ivr.DNIS

you can maybe find the offending rows with

select *
  from dim_call_segments s
  where TRY_CONVERT(numeric, DNIS) is null
3

Hi Yosiasz,

Thanks for the tip. After looking into it further, I see that in the dim_call_segments table, the "DNIS" column is set to (nvarchar(50), null) and the "DNIS" in the other table is set to (decimal(10,0), null). For the column thats set as nvarchar(50), should I do something like cast(DNIS as int)? What would you recommend?

Sajeel

4

Why do what seem to be identical columns have different data types. Also why would you have a decimal data type for phone numbers? I would not recommend casting as it does not address the fundamental issue of data types. And you will get an error anyways

select cast('206-666-6666' as int)

Also are you on Microsoft SQL Server and if so what version? What I recommend is what I have already addressed: identical data should have identical data type.

1 Like
5

I'm not sure why the person that built these tables, made the datatypes different. There are no dashes in the phone numbers. so shouldn't the cast function work?

6

so why would you cast to int when the other column is decimal(10,0)? You would be doing the same misstep as person that designed the tables.

but obviously there is something in there that is causing the original error.

Are you on Microsoft SQL server?

try this out to find which rows have issues

select *
  from dim_call_segments s
  where TRY_CONVERT(decimal(10,0), DNIS) is null

or

select *
  from dim_call_segments s
  where TRY_CONVERT(int, DNIS) is null

it would be much easier to conver the decimal(10,0) to nvarchar than the other way around. Proceed with caution.

7

Yes I'm on Microsoft SQL Server. Thanks for your help...I'll try it out. I appreciate it.

Sajeel

8

I converted the decimal(10,0) to nvarchar, and it worked. I appreciate your help.

Thanks,
Sajeel

1 Like