Link to home
Start Free TrialLog in
Avatar of Stevie Zakhour
Stevie Zakhour

asked on

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

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!
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

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!).
Avatar of Stevie Zakhour
Stevie Zakhour

ASKER

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
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...
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.
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
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.
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
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

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.
ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks mate, that worked. Now I know how to tackle this error in the future. Appreciate it!