StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>The only problem is that, no matter what I try to do, I can't use my alias in the where clause. I can't find any documentation on the subject</p> </blockquote> <p>Yes, the documentation for Access/Jet/ACE 'SQL' language is severely lacking and the little that is available has shocking errors.</p> <p>Here's some documentation about SQL generally:</p> <p>"Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL", ch12, pp235-237:</p> <blockquote> <p>Here is how a <code>SELECT</code> works in SQL... Start in the <code>FROM</code> clause... Go to the <code>WHERE</code> clause... Go to the optional <code>GROUP BY</code> clause... Go to the optional <code>HAVING</code> clause... Go to the <code>SELECT</code> clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the <code>SELECT</code> are done after all the other clauses are done. The <code>AS</code> operator can also give names to expressions in the <code>SELECT list</code>. These new names come into existence all at once, but after the <code>WHERE</code> clause, <code>GROUP BY</code> clause and <code>HAVING</code> clause have been executed; you cannot use them in the <code>SELECT</code> list or the <code>WHERE</code> clause for that reason.</p> </blockquote> <p>I think this explains why you cannot use an <code>as clause</code> ("column alias") in the <code>WHERE</code> clause in Access (Jet, ACE, whatever).</p> <p>That said, note that Access is non-compliant with SQL in that it allows you to use an <code>as clause</code> in the <code>SELECT</code> clause in left-to-right direction e.g. this is legal in Access SQL (but illegal in Standard SQL):</p> <pre><code>SELECT 2 AS a, 2 AS b, a + b AS c FROM tblMyTable </code></pre> <p>... thus proving that Access SQL is not really a SQL at all!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload