MS Access project with SQL Server

Author

long SQL Formulas

Stephen

2005-06-12, 3:24 am

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

2005-06-12, 3:24 am

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, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Stephen" < stephen@thesecongrou
p.com> wrote in message
news:ejPpp8wbFHA.1040@TK2MSFTNGP10.phx.gbl...
>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

2005-06-12, 3:24 am

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Stephen" < stephen@thesecongrou
p.com> wrote in message
news:ejPpp8wbFHA.1040@TK2MSFTNGP10.phx.gbl...
>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
>
>

Sponsored Links