The development scope
of PROC SQL and its aliasing rules predate the scope and rules of
the first ANSI SQL standard and the ISO SQL standard. In PROC SQL,
a column alias can be used in a WHERE clause, GROUP BY clause, HAVING
clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard,
the value that is associated with a column alias does not need to
be available until the ORDER BY clause is executed. As a result, there
is no guarantee that an SQL processor can resolve a column alias in
time for it to be referenced in a WHERE clause, GROUP BY clause, or
HAVING clause. Because
the ANSI SQL standard and ISO SQL standard require that a column alias
needs only to be available for reference when the ORDER BY clause
is executed, avoid writing code that refers to a column alias in a
WHERE clause, GROUP BY clause, or HAVING clause.
There are six parts
in the conceptual order of execution of a SELECT statement from the
ANSI SQL standard or ISO SQL standard perspective. If all six parts
exist, the sequence is the following:
-
The FROM part is executed first.
-
The WHERE part is executed second.
-
The GROUP BY part is executed third.
-
The HAVING part is executed fourth.
-
The SELECT part is executed fifth.
-
The ORDER BY part is executed last.
The only required parts of an SQL query are the SELECT
clause and FROM clause. The other four parts might be optional, depending
on what type of query you are performing.
Here is a high-level
template of an SQL query. The number enclosed in parentheses to the
right of each part represents its position in the conceptual order
of execution.
select <SELECT list> (5)
from <FROM clause> (1)
where <WHERE clause> (2)
group by <GROUP BY clause> (3)
having <HAVING clause> (4)
order by <ORDER BY clause>; (6)
In the following code
examples, the first alias in each SELECT statement is just a rename
of a table column. The second alias refers to a calculated expression.
The first and second SQL statements output the expected results in
PROC SQL.
/* --Preferred SQL code example since a column alias
is not referenced in the WHERE clause*/
/*-- Portable to other SQL processors --*/
select qty as Quantity, cost, cost+100 as ListPrice
from calc
where qty > 5;
/*-- This code example will work in PROC SQL,
but might not work with other SQL processors --*/
select qty as Quantity, cost, cost+100 as ListPrice
from calc
where Quantity > 5;
An early extension to
PROC SQL development was the CALCULATED keyword. The CALCULATED keyword
enables PROC SQL users to reference column aliases that are associated
with calculated expressions. The column alias referenced by the CALCULATED
keyword can be in the WHERE clause, GROUP BY clause, HAVING clause,
or ORDER BY clause. Using the CALCULATED keyword can be redundant
if it is used in the ORDER BY clause to refer to a column alias. That
column alias will have been already resolved by the time the ORDER
BY clause is executed.
Here is a PROC SQL code
example that uses the CALCULATED keyword to subset the rows by the
values that are associated with the second alias (ListPrice).
CALCULATED Keyword and the PROC SQL Use of Column Aliases
/*-- PROC SQL use of the CALCULATED keyword --*/
select qty as Quantity, cost, cost+100 as ListPrice
from calc
where CALCULATED ListPrice > 1500;
The ISO SQL standard-
and ANSI SQL standard-approved way of accomplishing this task is as
follows:
CALCULATED Keyword and the PROC SQL Use of Column Aliases
/*-- PROC SQL use of the CALCULATED keyword --*/
select qty as Quantity, cost, cost+100 as ListPrice
from calc
where cost+100 > 1500;
The code in the previous
example is portable.