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