Calculated field in a calculation. -- sql server mseq

Vishal Parkar
5/1/2004 7:27:51 AM

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

anonymous NO[at]SPAM discussions.microsoft.com
5/3/2004 12:10:35 PM

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?
[quoted text, click to view]

Steve Kass
5/4/2004 1:53:10 AM

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

[quoted text, click to view]

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 about sql server mseq

sql server mseq : Calculated field in a calculation.