Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Posts
    19

    Red face Unanswered: in T-SQL can I use a formula from a table cell?

    I'm not sure if this can be done and also maybe what to call it to search the net for answers.

    Basically I'm looking at a system where formulas have been added into fields in a table and I need to look at the field to see what formula to use when selecting eg: eg this + this, this / this etc.

    Here's a basic table I have knocked up to try different things...

    CREATE TABLE #HeaderOrder(
    [HeaderCode] [varchar](10) NOT NULL,
    [HeaderCode2] [varchar](10) NOT NULL,
    [FormulaCode] [varchar](10) NOT NULL
    )
    ;

    INSERT INTO #HeaderOrder VALUES ('ORD123','100','321+456')
    ;
    INSERT INTO #HeaderOrder VALUES ('ORD123','200','789')
    ;
    INSERT INTO #HeaderOrder VALUES ('ORD123','300','321/456')
    ;
    INSERT INTO #HeaderOrder VALUES ('ORD987','400','321-456')
    ;
    INSERT INTO #HeaderOrder VALUES ('ORD987','500','789')
    ;

    CREATE TABLE #HeaderLine(
    [HeaderLineCode] [varchar](10) NOT NULL,
    [OrderValue] [decimal](38, 20) NOT NULL
    )
    ;

    INSERT INTO #HeaderLine VALUES ('321','15000')
    ;
    INSERT INTO #HeaderLine VALUES ('456','5000')
    ;
    INSERT INTO #HeaderLine VALUES ('789','2500')
    ;

    -- check table OK
    SELECT * FROM #HeaderOrder;
    SELECT * FROM #HeaderLine;

    -- These would go together as a SUM because of '321+456'
    SELECT a.HeaderCode,a.HeaderCode2
    FROM #HeaderOrder a
    WHERE a.HeaderCode = 'ORD123'
    AND a.HeaderCode2 = '100'
    ;
    SELECT SUM(b.OrderValue) AS [OrderValue]
    FROM #HeaderLine b
    WHERE b.HeaderLineCode in ('321','456')
    ;

    -- These would go together as a DIVIDE because of '321/456'
    SELECT a.HeaderCode,a.HeaderCode2
    FROM #HeaderOrder a
    WHERE a.HeaderCode = 'ORD123'
    AND a.HeaderCode2 = '300'
    ;
    SELECT SUM(b.OrderValue) / SUM(c.OrderValue)
    FROM #HeaderLine b,
    #HeaderLine c
    WHERE b.HeaderLineCode in ('321')
    AND c.HeaderLineCode in ('456')
    ;

    This is what the end result should be when run on the whole table.

    -- End Result
    CREATE TABLE #EndResultTable(
    [HeaderCode] [varchar](10) NOT NULL,
    [HeaderCode2] [varchar](10) NOT NULL,
    [OrderValue] [decimal](38, 20) NOT NULL
    )
    ;

    INSERT INTO #EndResultTable VALUES ('ORD123','100','20000')
    ;
    INSERT INTO #EndResultTable VALUES ('ORD123','200','250')
    ;
    INSERT INTO #EndResultTable VALUES ('ORD123','300','3')
    ;
    INSERT INTO #EndResultTable VALUES ('ORD987','400','10000')
    ;
    INSERT INTO #EndResultTable VALUES ('ORD987','500','2500')
    ;

    SELECT * FROM #EndResultTable;

    -- clean up
    DROP TABLE #EndResultTable;
    DROP TABLE #HeaderOrder;
    DROP TABLE #HeaderLine;

    Any ideas or point me in the right direction or let me know if it isnt possible.

    Thanks,

    RC

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The only way I could think of is to create a dynamic SQL in a stored procedure.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Posts
    19
    OK thanks Wim.

    I'm not too hot on Dynamics SQL, only know the basics. Any tips on what I should roughly search the net for to get an idea how to work it out?

    Thanks,
    RC

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    There are some here with higher mathmatical skills than myself, but is this not matrix mathmatics? I have done this in c# with multidimensional arrays. If I were you, I would google "matrix math in sql" and see what pops out.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2011
    Posts
    19
    Thanks Thrasymachus,

    Just googled 'matrix math in sql' and bloody hell
    Looks like I'm in for a late evening reading over the web pages and working out math matrix.

    Thanks for your help,
    RC

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you post the solution? I'm really curious.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jun 2011
    Posts
    19

    Cool

    OK No problem when it's worked out will post it up. I'm meeting a friend next week that I think if I don't manage to find an answer by then, he might.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are running SQL 2012, you could use something like:
    Code:
    SELECT TRY_Convert(int, '112/355')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da! My bad, I meant TRY_PARSE (Transact-SQL)
    Code:
    SELECT TRY_Parse(int, '112/355')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •