InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comSat, 06 Sep 2008 21:03:15 GMT20http://www.teradata.com/teradataforum/Topic11293-9-1.aspxIt kind of didn't turn out to be as easy as I thought, but neverthless here's one way... ( The objective was to get all the emps that comes under steve to be pulled out into a view along with their job desc (from jobtbl)...so instead of first recursively pulling out all emps under steve in employee table and then joining with jobtbl for desc (as would have been a normal approach if one were to just write a recursive query), I included it in the join from the very first step as part of the normal recursive code... thus avoiding any need for a last separate join ....I wouldn't bet that this as efficient as the normal recursive query where the join can be done at the end ...CREATE RECURSIVE VIEW STEVE_EMPS (EMPID, EMPNAME, MGRID, MGRNAME, JOBCODE, JOBDESC)ASSELECT E.EMP_ID, E.EMP_NAME, NULL, NULL, J.JOB_CODE, J.JOB_DESCFROM EMPLOYEE E INNER JOIN JOBTBL JON E.JOB_CODE = J.JOB_CODEAND E.EMP_NAME = 'STEVE'UNION ALLSELECT E.EMP_ID, E.EMP_NAME, HR_EMPS.EMPID, HR_EMPS.EMPNAME, J.JOB_CODE, J.JOB_DESCFROM EMPLOYEE E INNER JOIN HR_EMPSON E.MGR_ID = HR_EMPS.EMPIDINNER JOIN JOBTBL JON E.JOB_CODE = J.JOB_CODE;Fri, 02 May 2008 20:56:27 GMTjoedsilvahttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxThanks Joe for the reply.Yes, this can be done. Could you plz provide me an example ??Thu, 01 May 2008 03:23:48 GMTMonikahttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxIs there any reason why you can't define this final view itself as a recursive view ? (The final select in the recursive definition can actually be joined with other tables and filter conditions added ... )Wed, 30 Apr 2008 01:39:45 GMTjoedsilvahttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxThanks Jeff_o.Joe, any suggestion from your end.Wed, 30 Apr 2008 01:02:26 GMTMonikahttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxI am not sure why this rule is enforced. You can work around it by capturing the results of the recursive view into a new table and then using a view (recursive or regular) on that table.Mon, 28 Apr 2008 22:28:03 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxHi joe,Thanks for the reply. we can insert the data into table from recursive view.but, what i mean is: a view on a recursive view.i have created a recursive view as:replace recursive view emp_rv as select.......now i want a view on that recursive view ie.,replace view abc asselect * from emp_rv;This is not possible. Could you suggest on this ?? its giving error 6926.Plz helpFri, 25 Apr 2008 09:38:03 GMTMonikahttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxCORRECTIONHere is an example of a recursive view. Once you create the view you can insert FROM the view to a table.-- create a test tableCREATE SET TABLE foo (myname VARCHAR(100),id integer)PRIMARY INDEX ( id );--insert the valuesINSERT INTO FOO VALUES ('I_LIKE_TERADATA',1); INSERT INTO FOO VALUES ('RED_IS_MY_FAVORITE_COLOR',2);INSERT INTO FOO VALUES ('SEE_YOU_LATER',3);INSERT INTO FOO VALUES ('THIS_IS_A_REALLY_LONG_STRING_THAT_IS_REALLY_TOO_LONG',4);--Make the viewREPLACE Recursive VIEW MY_LOOKUP (myname, id ) As(Select substr(myname, 1, index(myname,'_')-1) || ' ' || substr(myname, index(myname,'_') +1, character_length(myname)) as myname, idFrom foo rootunion allSelect substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.idFrom MY_LOOKUP directwhere index(direct.myname,'_') >0);--get the dataselect * from MY_LOOKUP b where index(b.myname,'_')=0 ;--you can also insert from the view. foo_table must be created!insert into foo_table select * from MY_LOOKUP b where index(b.myname,'_')=0 ;Fri, 25 Apr 2008 08:02:36 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxHere is an example of a recursive view. Once you create the view you can insert FROM the view to a table.-- create a test tableCREATE SET TABLE foo (myname VARCHAR(100),id integer)PRIMARY INDEX ( id );--insert the valuesINSERT INTO FOO VALUES ('I_LIKE_TERADATA',1); INSERT INTO FOO VALUES ('RED_IS_MY_FAVORITE_COLOR',2);INSERT INTO FOO VALUES ('SEE_YOU_LATER',3);INSERT INTO FOO VALUES ('THIS_IS_A_REALLY_LONG_STRING_THAT_IS_REALLY_TOO_LONG',4);--Make the viewREPLACE Recursive VIEW MY_LOOKUP (myname, id ) As(Select substr(myname, 1, index(myname,'_')-1) || ' ' || substr(myname, index(myname,'_') +1, character_length(myname)) as myname, idFrom foo rootunion allSelect substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.idFrom MY_LOOKUP directwhere index(direct.myname,'_') >0)select * from LOOKUP bwhere index(b.myname,'_')=0 --get the dataselect * From MY_LOOKUP;--you can also insert from the view. foo_table must be created!insert into foo_table select * from my_lookup;Fri, 25 Apr 2008 07:56:51 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxLook in the Teradata Message manual. You have a with within a recursive with.Thu, 24 Apr 2008 11:02:28 GMTjeff_ohttp://www.teradata.com/teradataforum/Topic11293-9-1.aspxhi all,I have created a recursive view emp_rec.I am creating a view on that recursive view as select * from emp_rec where emp_id=6;its giving error Failure 6926 WITH [RECURSIVE].Any suggestion on this.Plz helpThu, 24 Apr 2008 07:47:30 GMTMonika