Re: Renaming operation in SQL
Date: 2000/03/20
Message-ID: <8b5bno$bjd$1_at_nnrp1.deja.com>#1/1
>> ... renaming operation used in SQL languages <<
The AS operator is part of the SQL-92 Standard. It can be used to give a name to scalar expressions or to tabular expressions, which is important because SQL can references schema objects only by name and not by location. Let's look at both uses:
SELECT emp_name, (salary + commssion) AS total_pay
FROM Personnel
ORDER BY total_pay DESC;
The ORDER BY clause in part of a CURSOR, which gets the result set of the SELECT ...FROM statement. Without the AS operator there is no way to refer to the (salary + commssion) column for sorting.
SELECT AVG(tot_salary)
FROM (SELECT dept, SUM(salary)
FROM Personnel GROUP BY dept) AS DeptTotal (dept, tot_salary);
The innermost query result and its columns are given names so that the
containing query can process them. Otherwise, you would have to use a
VIEW:
BEGIN
CREATE VIEW DeptTotal (dept, tot_salary)
AS SELECT dept, SUM(salary)
FROM Personnel GROUP BY dept;
SELECT AVG(tot_salary)
FROM DeptTotal;
DROP VIEW DeptTotal;
END; Yes, some SQL products -- not all -- can use position numbers such as "ORDER BY 1" to do GROUP BY or ORDER BY on a column. This is a deprecated feature in SQL-92 and was dropped from SQL-99.
--CELKO--
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Mar 20 2000 - 00:00:00 CET