Error converting data type varchar to numeric

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 6706 Views - Last Post: 15 April 2016 - 12:51 PM

#1 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Error converting data type varchar to numeric

Posted 14 April 2016 - 11:52 AM

Normally I could debug this issue in a minute, but this one has me baffled as to why it is not converting.

This line of code has the error in it, after the multiplication sign. Error listed in subject

convert(decimal(10,4),isnull(Strike,1)) * convert(decimal(10,4),isnull(ABN.StrikeMult,1)) as Strike, 


This line of code works when converting to an int versus a decimal, but it doesn't calculate correctly because of it not being a decimal.

Max(Convert(decimal(10,4), InputTradePrice)) * Convert(int, isnull(ABN.StrikeMult,1)) as InputTradePrice


I am baffled because the column StrikeMult, has either 1 or 100 in every row. Is there some kind of erro r that I am not catching that could be occurring?

Is This A Good Question/Topic? 0
  • +

Replies To: Error converting data type varchar to numeric

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,791
  • Joined: 12-June 08

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 11:55 AM

I would verify 'Strike' and 'ABN.StrikeMult' are all numeric. Any particular reason why the columns would not be numeric off the bat?
Was This Post Helpful? 0
  • +
  • -

#3 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 11:56 AM

They aren't numeric off the bat because the import wizard was acting wonky when trying to import as numeric. So I had to import as varchar(200)
Was This Post Helpful? 0
  • +
  • -

#4 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:01 PM

Ok so I tried this:

select StrikeMult from ABNFutureSymbolMap where 
		isnumeric(isnull(StrikeMult,1)) <> 0


This query returned the rows that have values. So that means these values that were returned are not numeric, correct?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,791
  • Joined: 12-June 08

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:05 PM

Do you have any examples?

https://msdn.microso...y/ms186272.aspx
Was This Post Helpful? 0
  • +
  • -

#6 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:06 PM

Hopefully this is what you meant by example. This is my DB output.

StrikeMult
100
100
1
1
1
1
100
1
1
1
100
100
100
1
1
1
1
1
100
1
1

This post has been edited by jphoc13: 14 April 2016 - 12:10 PM

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,791
  • Joined: 12-June 08

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:17 PM

Any extra spaces, invalid characters, etc?
Was This Post Helpful? 0
  • +
  • -

#8 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:19 PM

There are white spaces...


To which I attempted this earlier:

convert(decimal(10,4),isnull(Rtrim(ABN.StrikeMult),1)) as Strike,

This post has been edited by jphoc13: 14 April 2016 - 12:20 PM

Was This Post Helpful? 0
  • +
  • -

#9 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:21 PM

try using RTRIM and LTRIM on your field before doing the convert. RTRIM(LTRIM(isnull(ABN.StrikeMult,1)))
Was This Post Helpful? 0
  • +
  • -

#10 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:23 PM

Yeah I tried the Trim earlier as well... No luck
Was This Post Helpful? 0
  • +
  • -

#11 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:24 PM

(I can't edit my post for what ever reason)
Alternatively, just because it looks like whitespace may not be the case. It could be a non-printable character or other non-whitespace thing. Try this link for that: http://www.sqlserver...0321-338-1.aspx
Was This Post Helpful? 0
  • +
  • -

#12 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 12:28 PM

I think what I may have to do is try to import the file again and see if I could make it import as a numeric.
Was This Post Helpful? 0
  • +
  • -

#13 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 14 April 2016 - 02:12 PM

I fixed my issue that I had in the OP. But now struggling with a logic one.

If I have an or statement that follows an inner join

Let's say:

FC.EXCH = ABN
or
FC = ABN

If I only want it to join on one or the other and not both, which it does in some cases, how do I prevent that?

Full code here:

Select FuturesCode,
		Exchange,
		ABN.DisplaySymbol
From	ClearingFutures
		inner join RivalAccountMap on ClearingFutures.AccountIdentifier = RivalAccountMap.Account
		left join ABNFutureSymbolMap AS ABN
              on  RTRIM(ClearingFutures.FuturesCode) + '.' + RTRIM(ClearingFutures.Exchange) = ABN.ABNSymbol or 
			  RTRIM(ClearingFutures.FuturesCode) = ABN.ABNSymbol


It is also possible that my "or" is not the issue here.

Nevermind! Typing that helped me visualize what I was doing wrong! Thanks for all the help! lol
Was This Post Helpful? 0
  • +
  • -

#14 jphoc13   User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 584
  • Joined: 08-July 13

Re: Error converting data type varchar to numeric

Posted 15 April 2016 - 07:59 AM

Ok so my issue from my comment above wasn't fixed as I thought it was.

It is probably because I don't know the proper SQL syntax to accomplish this, or I am just not seeing the logical way around this.

Select FuturesCode,
Exchange,
ABN.DisplaySymbol
From	ClearingFutures
inner join RivalAccountMap on ClearingFutures.AccountIdentifier = RivalAccountMap.Account
left join ABNFutureSymbolMap AS ABN
              on  RTRIM(ClearingFutures.FuturesCode) + '.' + RTRIM(ClearingFutures.Exchange) = ABN.ABNSymbol or 
RTRIM(ClearingFutures.FuturesCode) = ABN.ABNSymbol


The basic issue is that certain rows that are true in first or statement are also true in my second or statement.

I want to not execute the second or statement if the first or statement is true for that row(which an or statement is supposed to do right?). Or remove that row from this query if it is true in the first or statement.

This post has been edited by modi123_1: 15 April 2016 - 08:18 AM
Reason for edit:: fixed formatting

Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,791
  • Joined: 12-June 08

Re: Error converting data type varchar to numeric

Posted 15 April 2016 - 08:19 AM

Sound it out.

(A) or (not A AND B )

Where A and B are you conditions.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2