[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Arithmetic overflow error converting expression to data type smallmoney.

Posted on 2005-04-11
11
Medium Priority
?
5,429 Views
Last Modified: 2012-05-05
I've just changed my code from this:  

SELECT 'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(LastFillQuantity) AS MONEY),1),13)),
+'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(count(*) AS MONEY),1),13))

to this:

SELECT 'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(LastFillQuantity) AS MONEY),1),13)),
+CAST(ROUND(LastFillQuantity*LastFillPrice, 0) AS numeric) AS 'Total $',
+'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(count(*) AS MONEY),1),13))

and I'm erroring out w/this:  
Server: Msg 8115, Level 16, State 2, Line 12
Arithmetic overflow error converting expression to data type smallmoney.

how should I be handling the multiplication?
0
Comment
Question by:RLLewis
  • 5
  • 5
11 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13754250
change

LastFillQuantity*LastFillPrice


to
LastFillQuantity*CONVERT(money,LastFillPrice)
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13754281
i'm sure i've got this wrong - syntax errors.

+CAST(ROUND(LastFillQuantity*CONVERT(money,LastFillPrice) AS 'Total $',

or is it?

+CAST(ROUND(LastFillQuantity*CONVERT(money,LastFillPrice, 0) AS 'Total $',
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 150 total points
ID: 13754464
youve lost a few brackets along the way:

+CAST(ROUND(LastFillQuantity*CONVERT(money,LastFillPrice, 0))) AS 'Total $',
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:RLLewis
ID: 13754514
Server: Msg 189, Level 15, State 1, Procedure usp_Report, Line 23
The round function requires 2 to 3 arguments.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13754967
there seems to be an awful lot of casts in this stuff.    Could you post the table schema and ill see if i can tidy it up.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13755008
yes, here  ---  but i'm not sure what you mean by 'tidy it up'....i won't be making any table def changes

TraderId   char      
AccountId  char
PrevOrderNo char
Destination   char
Symbol     char
BuySell     char
LastFillQuantity  int
LastFillPrice       smallmoney
TimeOfExecution  datetime      
ClearingFee  int      
LiquidityFlag char      
Client   varchar      
OrderNumber   varchar
OrigOrderNo    char      
BasketId   char      
BasketNumber  char
Quantity    int
ExecutedQuantity   int
LeavesQuantity   int
OrderType  char
LimitPrice  smallmoney      
AvgFillPrice  smallmoney
OrderStatus  char      
StopPrice  smallmoney
TimeInForce   char
cntrpartyID   varchar
SequenceNumber  int      
0
 
LVL 9

Accepted Solution

by:
sudheeshthegreat earned 225 total points
ID: 13756031
use this:
SELECT 'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(LastFillQuantity) AS MONEY),1),13)),
+CAST(ROUND(LastFillQuantity*CAST(LastFillPrice AS money), 0) AS numeric) AS 'Total $',
+'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(count(*) AS MONEY),1),13))

>>Arithmetic overflow error converting expression to data type smallmoney
i guess it would be better if you use MONEY as the datatype in the table instead of SMALLMONEY wherever you are not sure that you won't exceed the limit.

0
 
LVL 1

Author Comment

by:RLLewis
ID: 13756228
alright, this is totally not what i was hoping to see here.  but i believe it's my error/oversight.  see, i am currently using this in a report:

SELECT 'Stocks Volume -   '+LTRIM(LEFT(CONVERT(char(16),CAST(SUM(LastFillQuantity) AS MONEY),1),13)),
+'Total # Trades - '+LTRIM(LEFT(CONVERT(char(16),CAST(count(*) AS MONEY),1),13))

which gives me 8 distinctly different lines in the rpt - like this:

 EndPoint    #Trades    Volume      
 -------- ------------- -------------
 AAAA            70,244    14,597,083
 BBBB             7,720     5,603,479
 CCCC              1,209       146,100
 DDDD             2,419       241,900
 EEEE             2,101     1,409,737
 FFFF           426       266,482
 GGGG            25,117     9,819,180
 HHHH             2,356     1,228,603

all i wanted to do was add the value of each endpoint's trading (lastfillquantity * lastfillprice) and provide a total on the report.  like this:

 AAAA            70,244    14,597,083   somebignumber

now, while my aritmetic ovrerflow is gone, i have added 11,703 lines of aggregate to the report where i expected to see no additional lines, just the new value.
i need to revisit this data

0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13760803
Which column contains the EndPoint ?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13761275
destination = endpoint
it was my error w/the calculation -  here's the right way:

RIGHT('         ' + ISNULL(CONVERT(varchar(20),SUM(LastFillQuantity*CAST(LastFillPrice AS Money)),1),0.00),20) AS 'Total $'

thank you, shogunwade.  i will award the points, as you helped me, i just didn't clearly state what i was needing to do
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13761320
You're welcome.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question