sql server programming: long SQL View formula -- sql server programming

long SQL View formula

Stephen
6/11/2005 11:37:08 PM

sql server programming: 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

RE: long SQL View formula

John Bell
6/12/2005 1:50:08 AM

Hi

You can use something like:,

CREATE VIEW vwtotalA
AS
SELECT Field1 * Field2 + Field3 - Field4 AS TotalA, Field1, Field2, Field3,
Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12,
Field13, Field 14
FROM MyTable

CREATE VIEW vwtotalB
AS
SELECT (TotalA - Field5) * Field6 - Field7 AS totalB, totalA, Field1,
Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10,
Field11, Field12, Field13, Field 14
FROM vwtotalA

CREATE VIEW vwtotalC
AS
SELECT (TotalB * Field8 - Field9) * Field10 - Field11 AS TotalC,
totalB, totalA, Field1, Field2, Field3, Field4, Field5, Field6, Field7,
Field8, Field9, Field10, Field11, Field12, Field13, Field 14
FROM vwtotalB

CREATE VIEW vwtotalD
AS
SELECT (TotalC - Field12) * Field13 + Field14 AS TotalD, TotalC,
totalB, totalA, Field1, Field2, Field3, Field4, Field5, Field6, Field7,
Field8, Field9, Field10, Field11, Field12, Field13, Field 14
FROM vwtotalC

John

[quoted text, click to view]

Re: long SQL View formula

Steve Kass
6/12/2005 11:43:43 AM

Stephen,

You can do this with derived tables in a single view or query:

select
(TotalC- Field12) * Field13 + Field14 as TotalD
from (
select
Field12, Field13, Field14,
(TotalB * Field8 - Field9) * Field10 - Field11 as TotalC
from (
select Field8, Field9, Field10, Field11, Field12, Field13, Field14,
TotalA - Field5) * Field6 - Field7 as TotalB
from (
select Field5, Field6, Field7, Field8, Field9, Field10, Field11,
Field12, Field13, Field14,
Field1 * Field2 + Field3 - Field4 as TotalA
from yourTable
) T
) T
) T

I probably missed some columns here, but hopefully you get the idea.
Whether you do this, or use separate views, the SQL Server query
optimizer should not calculate the partial results more than once.

Steve Kass
Drew University

[quoted text, click to view]

Re: long SQL View formula

Stephen
6/13/2005 2:15:58 AM

John -

I was able to successfuly create the functionality required using 6
progressive views. It took a while and was rather tedious, but it works.

Thank you.

[quoted text, click to view]

Re: long SQL View formula

--CELKO--
6/13/2005 8:10:41 AM

You missed how a SELECT works. All the columns in a row are created at
once. The columns are identified by name and not by position. Thse
are a few of **many** reasons that columns are not fields.

Your choices are to write a formula from hell in the SELECT, to do the
computations in a front end language, to do the formula in a procedure,
to use derived tables or use VIEWs.

Another alternative is to build a look-up table and pre-calculate your
values. But this is probably not a good idea with with 20 variables.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.