Re: help with "as"

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> writes:

> > I tried some queries on the starter database coming with oracle 8.1.7 on linux. It seems strange to me that the rename operator "as" cannot work with range variable, nor with subquery. Here're two examples:
> >
> > 1)
> > select * from emp as E where E.deptno = 10;
> > Here I have to remove the "as" to make it work.
> >
> > 2)
> > select EN.*, dept.dname from
> > (select emp.deptno, emp.ename from emp where emp.job = 'CLERK') as EN, dept
> > where EN.deptno = dept.dept.no;
> >
> > I used to work with Cloudscape3.6, and similar usage of "as" worked pretty well there. Is this usage conforming to SQL92 or was I using Clouscape's extension?
> > Another strange(sort of) thing is that it seems oracle doesn't have any join keyword. Every join operation has to be written as operations on cartesian product.
>
> What is wrong with your second example from what I can see has nothing to do with your attempt at aliasing. The problem is your SQL. Try this:
>
> select emp.deptno as EN, emp.ename as dept
> from emp
> where emp.job = 'CLERK';
>
> It will work much better. Though why you would want to use a useless keyword is beyond me.
>
> Daniel A. Morgan

Daniel:
The second example is to illustrate a possible usage of aliasing subquery. It can be done without usign "as": select emp.deptno, emp.ename, dept.dname from emp, dept where emp.job = 'CLERK'; But consider the following:
select dept.dname, DN.high from
(select emp.deptno, max(emp.sal) as high from emp group by emp.deptno) as DN, dept where DN.deptno = dept.deptno;
Because only aggregate expression and the attribute to be grouped can appear in the group by clause, I think it'd be good to have aliasing subquery in this situation, otherwise we have to use a view instead of inlined view.

Thanks
XW Received on Thu Mar 01 2001 - 11:59:36 CST