Please help me in solving this query.

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

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

  • 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

  • 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

  • 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