Re: help with "as"

> 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 Received on Wed Feb 28 2001 - 23:20:36 CST