Error converting var char to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error converting var char to numeric

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2008-01-04 : 08:51:50

I need to try to get the field exceptname, however when I add this field into the query I get the error. If I don't include any fields from the excpname table, It works fine.

select distinct b.feetype, feename, excp_num--, exceptname
from pdeals a
join fee_names b on fee_type = b.feetype
join fee_dates c on b.feeid = c.feeid
join passn d on a.deal_num = d.deal_num
left join excpname e on a.excp_num = e.exceptname
where deal_eff is not null
and (deal_term > '20070101' or deal_term is null) and excp_num > 0

Here's the definition of the excpname table

CREATE TABLE [excpname] (
[exceptnum] [decimal](4, 0) NOT NULL ,
exceptname] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

What can I do?

Thanks,
Marcie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-04 : 08:55:48

What error do you get?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 08:56:58

whats the error obtained? Are you trying to insert this into some not null field?

memarcie
Yak Posting Veteran

91 Posts

Posted - 2008-01-04 : 09:05:52

The error is the title of the thread, Error converting varchar to numeric. I'm just doing a select, no insert.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 09:10:41

I'm going to take a guess here and say that it is happening here : left join excpname e on a.excp_num = e.exceptname
there are probably values in the column exceptname that are not numeric and can't be compared like this to a numeric value that is in the column excp_num,

but ddl and sample data would make things easier.

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 09:11:08

select distinct b.feetype, feename, excp_num--, exceptname
from pdeals a
join fee_names b on fee_type = b.feetype
join fee_dates c on b.feeid = c.feeid
join passn d on a.deal_num = d.deal_num
left join excpname e on a.excp_num =e.[exceptnum] e.exceptname
where deal_eff is not null
and (deal_term > '20070101' or deal_term is null) and excp_num > 0
I think you should use the [exceptnum] field for taking join.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-04 : 09:11:50

the fields you're joining on, are they the same datatypes? i.e. a char field joining to a char, an int joining to an int etc?

Em

memarcie
Yak Posting Veteran

91 Posts

Posted - 2008-01-04 : 09:13:26

Duh, missed that I was joining on excp_num to exceptname. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources