Convert when multiplying smallints

Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
ThanksTry,
SELECT cast(Quantity as bigint) * UnitCost FROM Transactions
AMB
"hals_left" wrote:

> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>|||Which of those statements produce the error? And do you know what values are
producing the error? Small int can go upto 32,767
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
Thanks|||SELECT Cast(Quantity as Bigint)*Cast(UnitCost as Bigint) FROM Transactions
works...but best option?
Lee-Z
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>

Convert real => decimal (3,2)

Folks,
How can I convert a real value to a decimal value? As long as the real
value is not zero I am getting an arithmetic overflow error.
I tried both convert and cast.
Does anyone have an idea?
Cheers
StephanzHI Thiere
there is no need of type costing ...it is implecit type costing betweer
real and decimal numbers.
_
________________________________________
________
"Stephan Zaubzer" wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz
>|||Hmm - i can only duplicate if the real has more than 1 digit to the left
of the decimal... (implicit or explicit conversions)
e.g., 1.234 converts fine, but 12.34 does not
Stephan Zaubzer wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz|||On Thu, 22 Sep 2005 19:11:47 +0200, Stephan Zaubzer wrote:

>Folks,
>How can I convert a real value to a decimal value? As long as the real
>value is not zero I am getting an arithmetic overflow error.
>I tried both convert and cast.
>Does anyone have an idea?
Hi Stephan,
As Trey already indicated: you will get this error for data that is
above 9.99 or below -9.99. In the notation "decimal(3,2)", the first
number (3) is the TOTAL number of positions; the second number (2) is
the number if digits after the decimal point. That leaves only one digit
for the integer part.
If your values range from -999.99 to 999.99, use DECIMAN(5,2) instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)