Re: How to name a subquery?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 23 Apr 2005 06:24:53 -0700
Message-ID: <1114262693.571983.288080@z14g2000cwz.googlegroups.com>

As Rene said I cannot tell squat from your post. From your request to name a subquery I would guess what you really want is the SELECT WITH feature which is also called sub-query factoring. The general form is below:

With
  avg_dept_sal as (select deptno, avg(sal) as deptavg

                   from emp
                   group by deptno)
 ,avg_sal      as (select avg(sal) as coavg from emp)

select empno, ename, job, deptno, sal
from emp e
where sal > (select deptavg from avg_dept_sal b

              where b.deptno = e.deptno) and sal > (select coavg from avg_sal) order by deptno, ename, job;

This isn't a great expample as the WITH clause is most useful when the same subquery needs to appear several times in the query.

The other probable feature you are asking for is how to place a label on an inline view:

select a.col1, a.col2, b.col3
from table_a a,

         (select d.col3 from table_d d where .... ) b <== label so d.col3 is now b.col3 to outer query
where a.col1 = b.col1
...

HTH -- Mark D Powell -- Received on Sat Apr 23 2005 - 08:24:53 CDT