Re: Aliases and the WHERE clause

I see.

The one hope we could hold is that future releases of engines will build on
prior shortcomings - (I'm still trying to get a handle on all the acronyms
related to those engines and technologies).

I'm not an expert on any of these technologies. However, when I first
dabbled in VB, it was clear that i.e. principles of modular programming did
not just suddenly arrive but evolved over time. By the time I sat down to
learn it, my reaction was: "wow, this is so common sense."

To me, SQL also feels just like a set of principles, or if you wish, mental
gymnastics that run on common sense (algebra and sets), which are executed by
similar but slightly different technologies in the background (depending on a
vendor). Perhaps due to the broader audience of users, it just might need a
bit more time to mature on some items - i.e. aliases - one can call it a
better "market" consensus.

We just have to continue arming ourselves with a ton of patience.

I appreciate your insight. I'm almost done with this learning-project but
there will have to be some more practice over the coming weeks. I'm
transitioning from the corporate operational side to accounting and the
ability to QUICKLY massage data is becoming more and more useful.

Thanks again,
Pepe


"Michel Walsh" wrote:

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: 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: Aliases and the WHERE clause
    ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ... based on other aliases). ... Note that MS SQL Server cannot use expression ...
    (microsoft.public.access.queries)
  • Re: Using Case colum in where clause
    ... Thanks but Hugo came up with that one - it just seems a shame that SQL can't ... use a derived column's alias in a where clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: Using Case colum in where clause
    ... >it makes it easier to understand n the where clause. ... >Odd that SQL doen't allow the use of table Alias names in where clauses - ho ... Not odd at all - very logical if you consider how queries ...
    (microsoft.public.sqlserver.programming)
  • 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)