June 19, 2008 at 5:59 am
Hi all,
This is the actual query:
select case when substring(cast(vCurrencyValue as varchar),1,2)='0.' then replace(convert(varchar,cast(vCurrencyValue*100 as money),1),'.00','') else replace(convert(varchar,cast(vCurrencyValue as money),1),'.00','') end as vCurrencyValues,iDYNCurrencyId,vCurrencyType,vCurrencyDesc,bFCurImagePath,bBCurImagePath,vAltName from DYNCurrency where iDYNCatId=6 order by iOrderNo,vCurrencyValue
Its working fine but when it comes to where clause iDYNCatId=6 its giving error as arithmetic overflow as its is having a value
1234567897894561234567894.00. the datatype of vCurrencyValue is decimal (18,2).
How to solve this problem.
If the length of vCurrencyValue is > 18 then we can display it as it is.No need for replace function.
Please help.........
June 19, 2008 at 6:29 am
Your are casting to a money type which may not hold the result of a decimal(18.2) * 100
Try casting to a larger decimal type.
June 19, 2008 at 6:41 am
Hi
But i want the result in money only,If we cant get it then how to display it normally as we have a bigger value with vCurrencyValue column.
we need to check it first
June 19, 2008 at 7:13 am
Try
select
case
when vCurrencyValue < 1 then replace(convert(varchar,cast(vCurrencyValue*100 as decimla(20,2)),1),'.00','')
else replace(convert(varchar,cast(vCurrencyValue as decimal(20,2)),1),'.00','')
end as vCurrencyValues,
iDYNCurrencyId,vCurrencyType,vCurrencyDesc,bFCurImagePath,bBCurImagePath,vAltName
from DYNCurrency
where iDYNCatId=6
order by iOrderNo,vCurrencyValue
June 19, 2008 at 7:23 am
The result you're pointing to is 26 digits long before the decimal (if i counted correctly with all of my fingers and toes) so at very least you'd have to cast to numeric(28,2). Of course I'd probably go a few digits bigger just to be on the safe side.
Edit: I apparently double counted one of the toes in there:) the min length looks to be numeric(27,2).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply