Archives of the TeradataForum

Message Posted: Fri, 01 Sep 2006 @ 09:22:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Can someone tell me why my example Recursive query (taken essentially from a previous Teradata discussion) fails both in Queryman and Bteq?

     CT EMP (mgr smallint , emp smallint);

     INS INTO EMP VALUES (1,2);
     INS INTO EMP VALUES (2,3);
     INS INTO EMP VALUES (2,4);
     INS INTO EMP VALUES (4,5);
     INS INTO EMP VALUES (4,6);

     {Perhaps I should have a (1,1) since managers are real employees too!}

     CT MGR (mgr smallint, job_title char(10));
     INS INTO MGR VALUES (1,'ONE');
     INS INTO MGR VALUES (2,'TWO');
     INS INTO MGR VALUES (4,'FOUR');

     WITH RECURSIVE CLOUGH_TMP
     (mgr,emp,LEVEL) AS
     (
     SELECT mgr,emp,0
     FROM EMP
     WHERE JOB_TITLE='ONE'
     UNION ALL
     SELECT EMP.mgr,
     EMP.emp,
     CLOUGH_TMP.LEVEL+1
     FROM EMP,CLOUGH_TMP
     WHERE CLOUGH_TMP.emp=EMP.mgr
     AND CLOUGH_TMP.LEVEL<10
     )
     SELECT *
     FROM CLOUGH_TMP
     ORDER BY 3,1,2;

     *** Failure 3706 Syntax error: expected something between the beginning of
     the request and the 'WITH' keyword.

What is it that I'm missing before the 'WITH', if that is the real problem.

Thanks in advance ..

Dave Clough
Database Designer
Express ICS

www.tnt.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>