You Asked
Oracle has had the CONNECT BY syntax for some time. Competitors like DB2 and SQL Server 2005 use a "recursive Common Table Expression (CTE)", that is, a recursive named subquery, to achieve similar results:
</code>
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/ http://msdn2.microsoft.com/en-us/library/ms175972.aspx <code>
Oracle 10gR2, unless I missed it in the docs, still doesn't allow recursive named queries. Is this because CONNECT BY does at least what recursive queries allow (particularly since the ORA-01436 error has been removed for at least some cases)? In other words, recursive CTE would be no more than a syntax change, something like going from Oracle outer joins to ANSI join syntax?
and Tom said...
Well, I'll say connect by has been around for some time....
Anyone care to guess what version/year?
Version 2, 1977.
so, yes, connect by is an oldie but goodie. (and recursive queries in DB2 - they don't have the 1436 or NOCYCLE, they can be infinite loops according to the link you provided - careful with that...)
I don't know about you - but I find the understandability of:
1 SELECT name
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid
in Oracle versus:
1 WITH n(empid, name) AS
2 (SELECT empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT name FROM n;
in DB2 et.al. to be night and day difference. I'm not sure what the motivation to introduce this syntax would be entirely. Given that the databases are different and what works on DB2 doesn't necessarily work on SQLSserver and what works on SQLServer doesn't necessarily work on Oracle and in fact:
What works on Database X
does not necessarily work
on Database Y
is true.... I am not aware of any move to provide this syntax (especially given the fact that the HIERARCHY is busted totally with this "recursive" approach, the Oracle query returns:
1 SELECT level, rpad('*',2*level,'*')||name name
2 FROM emp
3 START WITH name = 'Goyal'
4* CONNECT BY PRIOR empid = mgrid
ops$tkyte@ORA10GR2> /
LEVEL NAME
---------- --------------------
1 **Goyal
2 ****Zander
3 ******McKeough
3 ******Barnes
2 ****Henry
3 ******O'Neil
3 ******Smith
3 ******Shoeman
2 ****Scott
9 rows selected.
whereas the db2 query produces this:
1 WITH n(level, empid, name) AS
2 (SELECT 1, empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT n.level + 1, nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT level, name FROM n;
LEVEL NAME
----------- ----------
1 Goyal
2 Zander
2 Henry
2 Scott
3 McKeough
3 Barnes
3 O'Neil
3 Smith
3 Shoeman
9 record(s) selected
I see we can simulate it on a "case by case basis, sometimes" by writing a UDF connect_by_pos function written in C, we could code this:
1 WITH source(empid, name, salary, mgrid, rownum) AS
2 (SELECT empid, name, salary, mgrid,
3 ROW_NUMBER() OVER(ORDER BY salary)
4 FROM emp),
5 n(empid, name, salary, level, pos) AS
6 (SELECT empid, name, salary, 1,
7 CONNECT_BY_POS('', 0, rownum)
8 FROM source
9 WHERE name = 'Goyal'
10 UNION ALL
11 SELECT nplus1.empid, nplus1.name,
12 nplus1.salary, level + 1,
13 CONNECT_BY_POS(n.pos, level, rownum)
14 FROM source AS nplus1, n
15 WHERE n.empid = nplus1.mgrid)
16 SELECT name, level, salary
17 FROM n
18 ORDER BY pos;
NAME LEVEL SALARY
---------- ----------- -----------
Goyal 1 80000.00
Henry 2 51000.00
Shoeman 3 33000.00
Smith 3 34000.00
O'Neil 3 36000.00
Zander 2 52000.00
Barnes 3 41000.00
McKeough 3 42000.00
Scott 2 53000.00
9 record(s) selected
I'll stick with connect by for now if you don't mind.
Addenda: June 2019
Note that recursive WITH was added in 11.2 of the Oracle database engine, so now you can use CONNECT BY *or* recursive WITH.
Rating
(9 ratings)
Is this answer out of date? If it is, please let us know via a Comment