Aliases and the WHERE clause

[Guest]

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

 

[Michel Walsh]

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

 

[Guest]

Thanks for the response. It certainly helps to know that.

Pepe

 

[Michel Walsh]

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

 

[Guest]

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

 

[Jamie Collins]

The one hope we could hold is that future releases of engines will build on

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

Michel corrected himself i.e. Jet *is* compliant with the SQL-92
standard as regards column correlation names ('field aliases') in the
WHERE clause of a SELECT query and Jet remains non compliant only as
the ORDER BY clause, which I consider to be merely a minor annoyance
and I rarely use 'server' side sorting anyhow.

Another way in which Jet is non-compliant is that the correlation names
are supposed to come into existence all at once, however Jet allows you
do so this

SELECT 0.40 AS tax_rate, earnings * tax_rate AS tax_amount, etc

I suggest you do not wait at all for such fundamental aspects of Jet to
change. Jet was effectively deprecated before Access2000 was released.
The Access team now have a 'private' copy but if you want an idea of
their priorities as regards the engine see:

http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx

Jamie.

--