Calculated field in a calculation.

In Access if
FieldA = FieldB + FieldC
And
FieldC = FieldD - FieldE,
I can use the statement
FieldA = FieldB + FieldC
but in SQL Server, I need to use
FieldA = Field B + (FildD-FieldE)
This works fine with a simple query. However, the queries
I would like to move to SQL Server Stored Procs have SQL
statements that run between 3 and 4 pages with many
calculated fields consisting of several other calculated
fields. How are such things (large queries with very
convoluted calculations) normally handled in SQL Server?
hi lind,
In RDBMS theory the computed expressions that are used in the SELECT
statements like
select (col1+col2) as 'exp1', (col3+col4) as 'exp2'
gets executed all at the same time and not one after another. Hence one
expression is not visible to other expression. Access handles these kind of
"query expressions" in different way, you really can not compare SQL written
in Access and SQL Server.
You can only reference these kind of expressions in Order by clause.
Ex:
use northwind
go
select customerid + 'x' as 'cusid'
from customers
order by cusid
--but following will be errored out.
use northwind
go
select customerid + 'x' as 'cusid', cusid + 'y' as 'cusid2'
from customers
The work around would be to use derived tables.
Ex:
use northwind
go
select cusid,cusid + 'y' as 'cusid2'
from
(select customerid + 'x' as 'cusid'
from customers) DerivedTable --this is evaluated as derived table
Vishal Parkar
vgparkar@.yahoo.co.in
|||So, what does one do in SQL server when one wants to
create a view or stored procedure from an Access query
whose SQL statement runs 4 or 5 pages with many calculated
fields using other calculated fields. The only solution
seems to be to include the formula for any calculated
expression in the calculation that uses it. Is this really
the way extremely complex SQL statments are handled?
>--Original Message--
>hi lind,
>In RDBMS theory the computed expressions that are used in
the SELECT
>statements like
>select (col1+col2) as 'exp1', (col3+col4) as 'exp2'
>gets executed all at the same time and not one after
another. Hence one
>expression is not visible to other expression. Access
handles these kind of
>"query expressions" in different way, you really can not
compare SQL written
>in Access and SQL Server.
>You can only reference these kind of expressions in Order
by clause.
>Ex:
>use northwind
>go
>select customerid + 'x' as 'cusid'
>from customers
>order by cusid
>--but following will be errored out.
>use northwind
>go
>select customerid + 'x' as 'cusid', cusid + 'y'
as 'cusid2'
>from customers
>The work around would be to use derived tables.
>Ex:
>use northwind
>go
>select cusid,cusid + 'y' as 'cusid2'
>from
>(select customerid + 'x' as 'cusid'
>from customers) DerivedTable --this is evaluated as
derived table
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in
>
>.
>
|||Two possibilities are derived tables and views:
select x+y as xy, y+z as yz
from (
select a*b+c as x, d*e+f as y, z
from (
select a, w-v as b, ...
from T join U
on ...
where ...
) ABC
) XYZ
or
create view V1 as
select a, w-v as b, ...
from T join U
on ...
where ...
go
create view V2 as
select a*b+c as x, d*e+f as y, z
from V1
go
and so on.
The primary purpose of SQL is not to perform nested arithmetic
calculations, which may be part of why not a great deal of effort went
in to making that one of the easiest things to do, but neither of these
solutions is likely to be inefficient.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>So, what does one do in SQL server when one wants to
>create a view or stored procedure from an Access query
>whose SQL statement runs 4 or 5 pages with many calculated
>fields using other calculated fields. The only solution
>seems to be to include the formula for any calculated
>expression in the calculation that uses it. Is this really
>the way extremely complex SQL statments are handled?
>
>the SELECT
>
>another. Hence one
>
>handles these kind of
>
>compare SQL written
>
>by clause.
>
>
>as 'cusid2'
>
>derived table
>