Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: help with "as"

Re: help with "as"

From: Spencer <spencerp_at_swbell.net>
Date: Fri, 2 Mar 2001 21:41:54 -0600
Message-ID: <iJZn6.861$RU5.6850@nnrp1.sbc.net>

"Xiaowen Wang" <swangken_at_cfdlab.ae.utexas.edu> wrote in message news:m2wva9xtdj.fsf_at_speedo.happyhome.org...
> "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
>

the "AS" keyword is not allowed when giving an alias for a table. it is optional when providing an alias for an expression in the select list. it looks like your examples should work fine if you leave out the invalid AS keyword.

i have no idea whether or not your usage of this reserved word conforms to the SQL92 standard or not, but i do know that it does NOT conform to the Oracle syntax rules. Received on Fri Mar 02 2001 - 21:41:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US