asked on

Arithmetic overflow error converting expression to data type smallmoney.

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?

ShogunWade

change

LastFillQuantity*LastFillPrice

to
LastFillQuantity*CONVERT(money,LastFillPrice)

RLLewis

ASKER

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 $',

SOLUTION

ShogunWade

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RLLewis

ASKER

Server: Msg 189, Level 15, State 1, Procedure usp_Report, Line 23
The round function requires 2 to 3 arguments.

ShogunWade

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.

RLLewis

ASKER

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

ASKER CERTIFIED SOLUTION

sudheeshthegreat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RLLewis

ASKER

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

ShogunWade

Which column contains the EndPoint ?

RLLewis

ASKER

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

ShogunWade

You're welcome.