error, not sure what it means - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

error, not sure what it means

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-08 : 12:41:24

I get "Arithmetic overflow error converting varchar to data type numeric."

i think its because my code:
, case when pe.Book_ISBN is null and pe.Quickbooks_Item_CogsAccnt is null and pe.SalePrice is null then pe.productprice else case when pe.Book_ISBN is null and pe.Quickbooks_Item_CogsAccnt is null then pe.SalePrice else case when pe.Book_ISBN is null then pe.Quickbooks_Item_CogsAccnt*1.25 else pe.Book_ISBN end end end as "ItemPrice"

the 'quickbooks_item_cogsaccnt' is all numbers, but its stored as text? not sure....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-12-08 : 12:56:03

I think you are mixing up different column types...different rows are returning different values for ItemPrice - some numeric and some varchar and SQL Server is telling you it cant convert the text to numbers...check your schema and the data in the tables...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-08 : 12:57:51

I know every peice of data in there is an actual number

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-08 : 13:10:48

Quickbooks_Item_CogsAccnt it is for sure

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:22:53

You may need to cast it as int or decimal before doing the arithmetic..

case when pe.Book_ISBN is null 
        and pe.Quickbooks_Item_CogsAccnt is null 
        and pe.SalePrice is null 
 then pe.productprice 
else case when pe.Book_ISBN is null 
          and pe.Quickbooks_Item_CogsAccnt is null 
     then pe.SalePrice 
    else case when pe.Book_ISBN is null 
          then cast(pe.Quickbooks_Item_CogsAccnt as decimal(10,2)) * 1.25 
          else pe.Book_ISBN 
        end 
    end 
 end as "ItemPrice"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-08 : 13:30:02

agreed:
check the datatypes of all these columns to make sure they are all numeric with similar scale and precision.

pe.Book_ISBN
pe.Quickbooks_Item_CogsAccnt
pe.SalePrice
pe.productprice

then rather than the nested CASE statements can you do a COALESCE giving preference to whichever columns you want?
perhaps:


coalesce(pe.Book_ISBN
       ,pe.Quickbooks_Item_CogsAccnt*1.25
       ,pe.SalePrice
       ,pe.productprice )

Also, it's good practice for both CASE and COALESCE to make sure that any item will result in the same datatype. ie: if the first row uses Book_ISBN and is numeric(3,2) but the 2nd row results in using SalePrice which may be money then sql server may attempt to convert the money value to numeric(3,2). If the value is large enough you'll get an arithmetic overflow error.

Be One with the Optimizer
TG

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-08 : 13:57:36

Perfect. Cast decimal (10,2) totally did it.

thanks guys~! You are always the best!!!!

Not sure what it does, but I know it works!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:58:36

Also have a look at TG's suggestion, that might work for you, and make your code better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources