We help IT Professionals succeed at work.

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

10,480 Views
Last Modified: 2018-01-17
Hi All

When running the below query SQL reports back saying "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric."

QUERY
select
a.first_name as First_Name
,a.last_name as Last_Name
,a.e_mail as E_Mail
,b.trans_no as Transaction_Number
,c.item
from guests a
inner join
trs_info b on
a.guest_no = b.trans_no
inner join
transact c on
b.trans_no = c.item

The table trs_info, column trans_no is all numeric, such as "2100200" whereas the table transact, column item is either numbers, such as "15:30" is varchar (mix of numbers or words\letters).

Any help is greatly appreciated!
Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
This looks like an issue with joining columns.

For all the columns involved, why don't run a sanity check? As in:

select trans_no
from trs_info
where isnumeric(trans_no) = 0

Open in new window


For the sake of resolving error just convert the columns to desired data types explicitly (suggesting with warning of performance degradation and highly discouraged practice!).

Author

Commented:
Hi Mate

Thanks for your help, I did run the check and the result is in the attached (see Sanity.tiff). How do I convert the columns to the desired data types?

Sorry, still learning SQL.
Sanity.tiff
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Assuming you want both to be varchar...for example...

from guests a inner join trs_info b on convert(varchar(10), a.guest_no) = convert(varchar(10), b.trans_no)

Open in new window


My hands shivering as I type this....very very very bad...
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Image suggests all good with trans_no. You will need to do this to all the columns of all the tables, involved in the join in the query you posted.

Author

Commented:
Hi Mate

Thanks for your help, I modified the query and ran it again. However this time SQL reports "Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar."

QUERY
select
a.first_name as First_Name
,a.last_name as Last_Name
,a.e_mail as E_Mail
,b.trans_no as Transaction_Number
,c.item
from guests a
inner join
trs_info b on
convert(varchar(10), a.guest_no) = convert(varchar(10), b.trans_no)
inner join
transact c on
b.trans_no = c.item

How do I tackle this?

Also, I did run another sanity check on the second table in the join, see below.

select item
from transact
where isnumeric(item) = 0

The output is in the attached (see Sanity 2).

Appreciate the help!
Sanity-2.tiff
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
The data is with you and (hopefully) you should be familiar with it. I gave 10 just as an example, may be your data is 20 char wide, I don't know. You can either be pragmatic about it or find out as in.

select max(len(guest_no)) from guests

Open in new window


In any case, guest_no and trans_no are probably not the culprits. So better remove any conversion there (was given as an example anyway!) And try applying it to item as it has given you list of non-numeric values. Convert both columns as before. How much to give in varchar(?) is up to the data.

Author

Commented:
OK, thanks. I'm still getting familiar with the data.

I found the below

Table trs_info, column trans_no: 12 char wide
Table transact, column item: 10 char wide
Table guest, column guest_no: 10 char wide

I might be doing something wrong here, I updated the query below

select
a.first_name as First_Name
,a.last_name as Last_Name
,a.e_mail as E_Mail
,b.trans_no as Transaction_Number
,c.item as Flown_Bock_Time
from guests a
inner join
trs_info b on
a.guest_no = b.trans_no
inner join
transact c on
convert(varchar(12), b.trans_no) = convert(varchar(10), c.item)

The result outputs no error but shows blank in the result pane (see attached).
Result.tiff
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Both should be identical. If you are getting diff length put identical number of max on both sides...

So, it should be...

convert(varchar(12), b.trans_no) = convert(varchar(12), c.item)

Open in new window

Author

Commented:
Thanks, I did try that thinking it may work but no luck. See below

select
a.first_name as First_Name
,a.last_name as Last_Name
,a.e_mail as E_Mail
,b.trans_no as Transaction_Number
,c.item as Flown_Bock_Time
from guests a
inner join
trs_info b on
a.guest_no = b.trans_no
inner join
transact c on
convert(varchar(12), b.trans_no) = convert(varchar(12), c.item)

Open in new window


I also tried the below

select
a.first_name as First_Name
,a.last_name as Last_Name
,a.e_mail as E_Mail
,b.trans_no as Transaction_Number
,c.item as Flown_Bock_Time
from guests a
inner join
trs_info b on
a.guest_no = b.trans_no
inner join
transact c on
convert(varchar(max), b.trans_no) = convert(varchar(max), c.item)

Open in new window


The output shows no result. Am I missing something here?

Thanks for your help.
Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks mate, that worked. Now I know how to tackle this error in the future. Appreciate it!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.