long SQL Formulas

[Stephen]

I have been migrating an MDB to SQL and have had to rewrite many of the
queries into SQL Views. The problem I am having is that SQL doesn't seem to
be able to use progressive formulas. Here is what I mean:

I have a commisison calculation that requires about 20 differnent variables.
Example ...(I have simplified this for sample purposes):

Field1 * Field2 + Field3 - Field4 = TotalA
(TotalA - Field5) * Field6 - Field7 = TotalB
(TotalB * Field8 - Field9) * Field10 - Field11 = TotalC
(TotalC - Field12) * Field13 + Field14 = TotalD

In and MDB, I can create a query with 4 simple calculations as listed above
(each of which can refer to a previous calculation (i.e. TotalA, TotalB,
TotalC)

However, when I try to do this with a SQL view, it does not recognize
TotalA, TotalB or TotalC. In order to write the above formula for the view,
I have to write:

((((((Field1 * Field2 + Field3 - Field4 ) - Field5) * Field6 - Field7) *
Field8 - Field9) * Field10 - Field11) - Field12) * Field13 + Field14

As you can imagine, with 20 variables, each of which requires significant
calculations, this formula becomes too long to process and completely
unmanageable to debug and modify. To make things more difficult, the view
is used to display the formula in a form for a user as well, so they can see
the calculation. As a result, each subtotal requires a calculation in the
view so the controls on teh form and use the specific field as a source.
Therefore I have to setup each stage of the formula as a separate
calculation. The View is then doing the following calculations:

Field1 * Field2 + Field3 - Field4 = TotalA
((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7 = TotalB
(((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7) *
Field8 - Field9) * Field10 - Field11 = TotalC
((((Field1 * Field2 + Field3 - Field4) - Field5) * Field6 - Field7) *
Field8 - Field9) * Field10 - Field11) - Field12) * Field13 + Field14 =
TotalD

It seems that I am asking the view to calculated the same figures multiple
times, thus making the view more complicated and thus, slower. If I realize
that TotalB is calculated wrong, I haev to then modify the formula for
TotalB and then make thos same changes to every foumla that uses TotalB as a
base for its calculation. NIGHTMARE! Help? Is there a way to refer to
"calculated fields" in a view without creating 20 separeate views, each
dependent on the prior view? Since SQL is used to evaluate very complex
calculations, it seems that this is not an unusual situation, so I imagine
there must be an easy way to to this is SQL.

-Stephen

 

[Sylvain Lafontaine]

First, whenever possible, computations should be done on the client side;
while the server side should be reserved to the extraction of data.

Second, you can probably use a UDF that will return a scalar value TotalD,
if you need only this value, or a table with the other required values.
(Sorry, I didn't test this with Views).

Another solution would be to use a sequence of subqueries. For exemple, for
the first level:

.... Select ((Q1.TotalA - Field5) * Field6 - Field7) as TotalB
From (Select (Field1 * Field2 + Field3 - Field4) as TotalA) as Q1 ....

Another possibility would be to use a temporary table (or a Cursor) inside a
SP to compute your values. Finally, if these values are often computed,
then maybe you could store them directly in the table.

 

[Sylvain Lafontaine]

Also, you should ask this question in a more appropriate newsgroup like
m.p.sqlserver.programming if you want to have the best possible answers.