CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Patrick
Patrick

Reputation: 2577

Sql column value as formula in select

Can I select a column based on another column's value being listed as a formula? So I have a table, something like: 

column_name    formula     val
one            NULL        1
two            NULL        2
three          one + two   NULL

And I want to do 

SELECT
    column_name,
    CASE WHEN formula IS NULL
        val
    ELSE 
        (Here's where I'm confused - How do I evaluate the formula?)
    END as result
FROM
   table

And end up with a result set like

column_name   result
one           1
two           2
three         3

Upvotes: 1

Views: 1524

Answers (2)

DancingFool
DancingFool

Reputation: 1267

You keep saying column, and column name, but you're actually talking about rows, not columns.

The problem is that you (potentially) want different formulas for each row. For example, row 4 might be (two - one) = 1 or even (three + one) = 4, where you'd have to calculate row three before you could do row 4. This means that a simple select query that parses the formulas is going to be very hard to do, and it would have to be able to handle each type of formula, and even then if the formulas reference other formulas that only makes it harder.

If you have to be able to handle functions like (two + one) * five = 15 and two + one * five = 7, then you'd be basically re-implementing a full blown eval function. You might be better to return the SQL table to another language that has eval functions built in, or you could use something like SQL Eval.net if it has to be in SQL.

Either way, though, you've still got to change "two + one" to "2 + 1" before you can do the eval with it. Because these values are in other rows, you can't see those values in the row you're looking at. To get the value for "one" you have to do something like 

Select val from table where column_name = 'one'

And even then if the val is null, that means it hasn't been calculated yet, and you have to come back and try again later.

If I had to do something like this, I would create a temporary table, and load the basic table into it. Then, I'd iterate over the rows with null values, trying to replace column names with the literal values. I'd run the eval over any formulas that had no symbols anymore, setting the val for those rows. If there were still rows with no val (ie they were waiting for another row to be done first), I'd go back and iterate again. At the end, you should have a val for every row, at which point it is a simple query to get your results.

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

Possible solution would be like this kind....but since you mentioned very few things so this works on your above condition, not sure for anything else.

  GO
  SELECT
  t1.column_name,
   CASE WHEN t1.formula IS NULL
    t1.val
   ELSE 
      (select sum(t2.val) from table as t2 where t2.formula is not null)
    END as result
   FROM
   table as t1
  GO

If this is not working feel free to discuss it further.

Upvotes: 0

Related Questions