Re: any syntax similar as " with select " clause in ibm db2?
From: Chris Leonard <s_p_a_m_chris_at_hotmail.com>
Date: Thu, 6 Mar 2003 11:48:42 -0600
Message-ID: <%RL9a.251$pW1.41373_at_news.uswest.net>
Date: Thu, 6 Mar 2003 11:48:42 -0600
Message-ID: <%RL9a.251$pW1.41373_at_news.uswest.net>
Thanks to padderz ... and to John for pointing it out to me ... I hadn't even noticed the introduction of the WITH / subquery factoring clause in 9i. That's a nice addition.
-- Cheers, Chris ___________________________________ Chris Leonard, The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com MCSE, MCDBA, OCP, CIW ___________________________________ "John" <johnwu_at_yorku.ca> wrote in message news:1673c1fd.0303040602.20407874_at_posting.google.com...Received on Thu Mar 06 2003 - 18:48:42 CET
> "Chris Leonard" <s_p_a_m_chris_at_hotmail.com> wrote in message
news:<4VK8a.7$G_.18385_at_news.uswest.net>...
> >
> > SELECT EMP.EMPNO, EMP.DEPTNO, DCOUNTS.TOTAL_EMPS
> > FROM EMP
> > JOIN (SELECT DEPTNO, COUNT(*) AS TOTAL_EMPS
> > FROM EMP GROUP BY DEPTNO) DCOUNTS
> > ON EMP.DEPTNO = DCOUNTS.DEPTNO;
> >
> Thanks you Chris, That's what I needed.
>
> A good new over this issue is that, as Padderz said (see the second
> message on this topic), Oracle 9i supports WITH clause. I tried that
> but unfortunately there is still different syntax applying WITH from
> the way applying in DB2.
>
> --
> Thanks
> John Wu