Re: Calculated field in a calculation.

anonymous_at_discussions.microsoft.com
Date: 05/03/04

  • Next message: Steve Kass: "Re: Calculated field in a calculation." 
    Date: Mon, 3 May 2004 12:10:35 -0700

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

  • Next message: Steve Kass: "Re: Calculated field in a calculation."

    Relevant Pages

    • Re: Matching Dates
      ... I'm specifically referring to calculations performed in the SQL (the ... a calculation doesn't have to bee too involved before it's ... SQL Server techniques on Jet and give newbies answers based on Non Access ...
      (microsoft.public.access.queries)
    • Re: Matching Dates
      ... calculation for that to happen. ... e.g. awareness that Jet has rich SQL DDL syntax via its ANSI-92 Query ... regurgitated on a seemingly daily basis, ...
      (microsoft.public.access.queries)
    • Re: Matching Dates
      ... faster than a disck fetch, ... I am assuming you mean using ans SQL query as ... is faster than a calculation. ... e.g. awareness that Jet has rich SQL DDL syntax via its ANSI-92 Query ...
      (microsoft.public.access.queries)
    • table
      ... about a dozen Linked ODBC Tables (from a SQL Server database), ... But how the heck can I see the SQL statement used to populate ... I want to see the SQL Statement used to populate MainTable. ...
      (comp.databases.ms-access)
    • Re: Matching Dates
      ... SQL Server and dot net. ... calculation for that to happen. ... SQL Server techniques on Jet and give newbies answers based on Non Access ...
      (microsoft.public.access.queries)