Re: Aliases and the WHERE clause

Hi,


I was thinking about the ORDER BY clause. The WHERE clause behaves exactly
the same way in Jet and in MS SQL Server as neither of the two engines allow
alias in the WHERE clause. If you use the grid, and add a criteria on an
expression, the expression, not the alias, will be copied, for your, in the
WHERE clause.

Technically, that is somehow related to the fact that the WHERE clause is
evaluated BEFORE The SELECT, and thus, the alias is not known when the WHERE
clause is involved.


Vanderghast, Access MVP

"pepenacho" <pepenacho@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2BFCFAF1-355B-44BF-8210-9153CC88E676@xxxxxxxxxxxxxxxx
Thanks for the response. It certainly helps to know that.

Pepe

"Michel Walsh" wrote:

Hi,



Unfortunately, you can't use alias in the WHERE clause, with JET
(probably a
long time bug that is now unlikely to be fixed because no one (not
enough,
anyway) seems to complain). You have to cut and paste the whole
expression
it aliases (and hope that expression is NOT itself an arithmetic
expression
based on other aliases). Note that MS SQL Server cannot use expression
based
on aliases, so, in the end, we are left with two "half buggy" engines, in
that respect.



Hoping it may help,
Vanderghast, Access MVP

"pepenacho" <pepenacho@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:83CD33B2-72C6-402C-A404-72E6F6928899@xxxxxxxxxxxxxxxx
Hello:

I recently took up the cause of learning ever so much more about SQL,
specifically reading "SQL Queries for Mere Mortals" as well as
"Microsoft
Jet
SQL for Access 2000." The ability to flex a query's muscles in MS
Access
has
come, more than once, very handy.

I ran into trouble with Aliases. For example

SELECT EarningsBeforeInterestAndTax AS Ebit FROM IncomeStatements WHERE
Ebit
0

The problem is: the WHERE clause does not recognize the new Alias. How
come?
It seems that I can successfully alias a table, but not a field?

Thanks,
pepenacho





.

Relevant Pages

  • Re: Aliases and the WHERE clause
    ... The one hope we could hold is that future releases of engines will build on ... To me, SQL also feels just like a set of principles, or if you wish, mental ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... ORDER BY clause or when using ODBC to linked MS SQL tables. ... I don't believe the query is from SQL2K since ... you can set an alias ...
    (microsoft.public.access.queries)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)
Loading