SQLTeam.com | Weblogs | Forums

Advice needed


I have this table:

Dimension   letter     value     Calculation
Length1       H         20         NULL
Length2       U         10         NULL
Total         T                    H + U

I need to calculate the total Length according to the formula described in column "Calculation". The table is then exported to a web application, The calculation con be either made in SQL (no idea how this could be achieved) or in c#, which gets rather complicated, but I guess I could find a solution.

Anybody an idea of how to do this with SQL?.


This is somewhat similar to your needs - maybe you can "fiddle" with it to make it fit your needs.


This seems to be of good help, though it's not easy to read.
Did I understood right that you are doing this in a iterative way: Every iteration -which is done by joining the template table with the data table - substitutes the next upcoming [x] statement of the formula by its value. In the described case of your link there would be 13 iterations; in my example only 2. At the end of the iterations, the formula has been transformed to an expression that in a second step can injected and executed in dynamic SQL. It's result is then inserted/updated in those rows with a formula.
I'm not sure yet about the second step but I go for it.

I don't understand the need for the "union all" and what "cte2" is meant for; to me it looks as if one could do it without. I'll tell you once I get started. Thanks