Re: Calculated field in a calculation.

From: Steve Kass (skass_at_drew.edu)
Date: 05/04/04 

Date: Tue, 04 May 2004 01:53:10 -0400

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:

>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
>>
>>
>>.
>>
>>
>>

Relevant Pages

  • Re: Newbie: VB-ADO help
    ... I create a table to store some calculation results because they ... 'END LOOP 1 and LOOP 2 ... -Now I have to loop again for a second set of calculations (dependent on X ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)
  • Re: Joining threads; why?
    ... fashion, but why not create three threads: one to attach to SQL, one to open ... the spreadsheet, and one to access the web site. ... calculations, still a background operation, requires all three datasets. ... you join threads and do not proceed until all three have delivered data. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: basic question
    ... calculations from the DB to the client really depends on what the ... as www.sqlservercentral.com (assuming you're using SQL Server) as the ... procs are doing. ...
    (microsoft.public.dotnet.general)
  • Re: Tricky ADO.NET question: joining data from database with a DataTable
    ... The thing is that the calculations which create the 'view' you meant ... calculations or database size optimizations. ... SQL Server, so T-SQL is out. ... > stockinfo and holdings are from disparate data sources, ...
    (microsoft.public.dotnet.framework)