[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
Medium Priority
5,429 Views
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
Question by:RLLewis
• 5
• 5

LVL 18

Expert Comment

ID: 13754250
change

LastFillQuantity*LastFillPrice

to
LastFillQuantity*CONVERT(money,LastFillPrice)
0

LVL 1

Author Comment

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

ID: 13754464
youve lost a few brackets along the way:

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

LVL 1

Author Comment

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

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

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

AccountId  char
PrevOrderNo char
Destination   char
Symbol     char
LastFillQuantity  int
LastFillPrice       smallmoney
TimeOfExecution  datetime
ClearingFee  int
LiquidityFlag char
Client   varchar
OrderNumber   varchar
OrigOrderNo    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

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

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:

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

ID: 13760803
Which column contains the EndPoint ?
0

LVL 1

Author Comment

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

ID: 13761320
You're welcome.
0

## Featured Post

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
Course of the Month19 days, 17 hours left to enroll