Re: Renaming operation in SQL

From: <joe_celko_at_my-deja.com>
Date: 2000/03/20
Message-ID: <8b5bno$bjd$1@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 CST