Post reply

SQL - To the Power of -1

  • September 23, 2008 at 3:47 am

    #93826

    SQL DB

    I have a column called 'Rate', and I want to do a calculation whereby it does...

    'Rate' to the Power of -1

    Any idea's?

    Thanks

  • September 23, 2008 at 3:54 am

    #875522

    Hi,

    You can use the POWER function...

    POWER(CAST(rate AS FLOAT), -1)

    Hth


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 23, 2008 at 4:00 am

    #875524

    Equivalent to 1.0/Rate

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • September 23, 2008 at 4:07 am

    #875525

    Thanks, so for the following returned data:

    SELECT *

    FROM tblPandL

    WHERE Currency not like 'GBP'

    AND Currency not like 'ZAR'

    AND Date >= '04/08/08'

    order by date desc

    I want to run the power function, i would:

    UPDATEtblPandL

    POWER(CAST(rate AS FLOAT), -1)

    WHERE Currency not like 'GBP'

    AND Currency not like 'ZAR'

    AND Date >= '04/08/08'

    Thanks

  • September 23, 2008 at 4:08 am

    #875527

    If you wanted to update the rate using the power function you would:

    UPDATE tblPandL

    SET rate = POWER(CAST(rate AS FLOAT), -1)

    ...

    ...


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 23, 2008 at 4:11 am

    #875529

    :):D:):D:):D

    Thank you very very much!

  • September 23, 2008 at 4:13 am

    #875530

    Looking at the performance of 1.0/n against POWER(n, -1) it seems that there is negligible difference. For a run on a table of 500,000 rows, on my heap of junk, both statements took 13 seconds and had the same execution plan...


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 23, 2008 at 4:17 am

    #875531

    Thanks, I only have something like 700rows of data so it run quick for me!

    Thanks again!

Viewing 8 posts - 1 through 7 (of 7 total)

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