Post reply

Decimal Places

  • May 4, 2006 at 3:50 pm

    #172320

    I have a table with Results column (data type = float) and a DecDigits Column (Data type = smallint).  I need to create a view to show the result column according to the DecDigits column for example:

    Results = 0.1256 with DecDigits = 2 ==> in View to show Results = 0.13

    Results = 3.6 with DecDigits = 0 ==> in View to show Results = 4

    Results = 3.14 with DecDigits = 1 ==> in View to show Results = 3.1

    Can anyone help?

    Thanks

  • May 4, 2006 at 4:29 pm

    #636111

    This isn't great, but it works:

    create table #tmp

    (DecVal Decimal(10,6),

    DecLen int)

    insert into #tmp values (99.5555, 2)

    insert into #tmp values (99.5555, 1)

    insert into #tmp values (99.5555, 3)

    select

     case DecLen

        when 1 then convert(decimal (10,1), DecVal)

        when 2 then convert(decimal (10,2), DecVal) 

        when 3 then convert(decimal (10,3), DecVal)

        when 4 then convert(decimal (10,4), DecVal)

        when 5 then convert(decimal (10,5), DecVal)

        else

            DecVal

     end

    from #tmp

    drop table #tmp

    Unfortunately, you can't have DecLen directly in the convert statement.

    You could also go with a dynamic SQL query.

  • May 4, 2006 at 4:43 pm

    #636112

    Played around a bit more and realized that the above will cause rounding.

    Here's another way that eliminates the CASE statement and doesn't round:

    select  LEFT(

                cast(DecVal as varchar(30)),

                CHARINDEX('.',cast(DecVal as varchar(30)))

                +DecLen)

    from #tmp

  • May 5, 2006 at 7:59 am

    #636237

    Pam,

    Thanks, your first post was what I needed.

    Regards,

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply