Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Recursive query syntax error?
 
From:   David Clough

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016