Post reply

Math Formula in a Variable

  • March 7, 2008 at 6:32 am

    #71188

    I have a scalar function to return a calculated value. In my table I have a column named LONG_FORMULA which stores a formula for calculating a new value based on dynamic data. Here is an example:

    {+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])

    After I process the values in the formula I end up with a variable containing the actual values in a statement. example:

    +((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))

    If I perform a select on this I get an answer: 4683.463200

    But if I want to return this value from the function it will only allow me to return the statement, not the answer.

    How can I get the function to return this answer (4683.463200)? When I do something like this it produces an error: :w00t:

    DECLARE @RET_VAL DECIMAL(13,2);

    DECLARE @Formula as varchar(255);

    -- function does stuff ..

    -- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function

    SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))';

    SET @RET_VAL = @Formula;

    I get an error: Arithmetic overflow error converting varchar to data type numeric.

  • March 7, 2008 at 6:58 am

    #786613

    You can use either xml.value (but it is a problem with variables), or you could use dynamic SQL do do this.

    DECLARE @RET_VAL DECIMAL(13,2);

    DECLARE @Formula AS VARCHAR(255);

    -- function does stuff ..

    -- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function

    SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))';

    DECLARE @v NVARCHAR(1000)

    SELECT @v = 'select ' + @Formula + ' as x'

    DECLARE @t TABLE (x DECIMAL(13, 2))

    INSERT INTO @t EXEC(@v)

    SELECT TOP 1 @RET_VAL = x FROM @t

    ps: there is no function to evaluate an arithmetic expression stored in a variable in T-SQL (but you could write one in CLR).

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • March 7, 2008 at 7:03 am

    #786616

    Sorry, on SQL Server 2000 you cannot use table variables for inserting into, so here is a version with temporary tables:

    DECLARE @RET_VAL DECIMAL(13, 2) ;

    DECLARE @Formula AS VARCHAR(255) ;

    -- function does stuff ..

    -- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function

    SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' ;

    DECLARE @v NVARCHAR(1000)

    SELECT @v = 'select ' + @Formula + ' as x'

    --create table #t (x DECIMAL(13, 2))

    INSERT INTO #t

    EXEC ( @v )

    SELECT TOP 1

    @RET_VAL = x

    FROM #t

    DROP TABLE #t

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • March 7, 2008 at 7:10 am

    #786625

    Thanks for the quick reply.

    So in 2000 I should create a temp table and insert to that temp table? Since I am in a function it wont let me create or execute? I could do it outside the function, any ideas?

  • March 7, 2008 at 7:22 am

    #786632

    You will find that you're going to need to do that outside of a function. Exec isn't allowed in a function.

    ----------------------------------------------------------------------------------
    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?

  • March 7, 2008 at 7:25 am

    #786636

    shogg (3/7/2008)

    Thanks for the quick reply.

    So in 2000 I should create a temp table and insert to that temp table? Since I am in a function it wont let me create or execute? I could do it outside the function, any ideas?

    Oops, I missed that you need this in a function 🙁

    On 2000 I think you will not be able to do this.

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • March 7, 2008 at 7:37 am

    #786650

    Thanks, very helpful - much appreciated! 🙂

  • April 16, 2008 at 3:20 am

    #802943

    :cool::w00t::Whistling:

  • November 14, 2008 at 6:39 am

    #898526

    Hi

    I am actually busy doing the same thing. I was wondering if you could show me the function that maps the variables to the actual value. It would be very much appreciated

    Thanks

  • November 14, 2008 at 1:53 pm

    #898870

    SELECT Value

    FROM Table

    WHERE Condition

    _____________
    Code for TallyGenerator

  • November 17, 2008 at 12:17 am

    #899186

    Hi

    Thanks for the reply but im not quite sure what you mean. I wanted to see the function that takes you from this

    {+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])

    to this

    +((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))

    dynamically

    Thanks

  • November 17, 2008 at 3:45 am

    #899226

    Hi

    Thanks for the reply but im not quite sure what you mean. I was looking fo a function that took you from this

    {+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])

    to this

    +((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))

    Dynamicaly. For many formulae at once

    Thanks

  • November 17, 2008 at 4:13 am

    #899233

    SELECT ([CONTRACT_PRICE]*PCP)+([FF&E_AMT]*[PCP]*[TAPP_CONTRACT])

    FROM TableWithValues

    But you're probably asking about writing a translator from one language to another.

    If that's the case then I don't see what it has to do with T-SQL.

    _____________
    Code for TallyGenerator

  • November 18, 2008 at 12:42 am

    #899713

    Hi

    Thanks for the reply but what i wanted to see was the function that takes you from this

    {+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])

    to this

    +((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))

    The help would be very much appreciated

    Thankyou in advance

  • November 18, 2008 at 6:04 am

    #899849

    You can say it as many times as you want, it doesn't make any more sense with repetition ;). Please provide sample data and an example explaining what you want, as I don't see why Sergiy's doesn't do what you're asking for. Are you trying to remove your { 's in favor of ( 's?

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 15 posts - 1 through 15 (of 16 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy