Hi!
I am getting a strange warning:
db2 => with rec(ename,empno,mgr,job,sal,lev) as
(select ename,empno,mgr,job,sal,0 as lev from emp where ename='KING'
union all select e.ename,e.empno,e.mgr,e.job,e.sal,lev+1 from rec
r,emp e where e.mgr=r.empno)
select * from rec;
db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.)
ENAME EMPNO MGR JOB SAL LEV ---------- ----------- ----------- --------- -------- ----------- SQL0347W The recursive common table expression "SCOTT.REC" may contain an infinite
loop. SQLSTATE=01605
KING 7839 - PRESIDENT 5000.00 0 JONES
7566 7839 MANAGER 2975.00 1 BLAKE
7698 7839 MANAGER 2850.00 1 CLARK
7782 7839 MANAGER 2450.00 1 SCOTT 7788
7566 ANALYST 3000.00 2 FORD 7902
7566 ANALYST 3000.00 2 ALLEN 7499 7698 SALESMAN 1600.00 2 WARD 7521 7698
SALESMAN 1250.00 2 MARTIN 7654 7698
SALESMAN 1250.00 2 TURNER 7844 7698
SALESMAN 1500.00 2 JAMES 7900 7698 CLERK
950.00 2 MILLER 7934 7782 CLERK
1300.00 2 ADAMS 7876 7788 CLERK
1100.00 3 SMITH 7369 7902 CLERK
800.00 3
14 record(s) selected with 1 warning messages printed.
I can supress it by setting +W in DB2OPTIONS, but I wonder how did the database conclude that there may be a possible infinite loop? The result
is correct, the same as displayed by Oracle. The course of my confusion
is the
SQL0347W The recursive common table expression "SCOTT.REC" may contain
an infinite loop. SQLSTATE=01605
warning. How did the engine conclude that there may be an infinite
recursion?
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 85:20:22 |
Calls: | 6,495 |
Calls today: | 6 |
Files: | 12,099 |
Messages: | 5,276,971 |