Recursive view

Database
Enthusiast

Recursive view

hi 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 help
9 REPLIES
Teradata Employee

Re: Recursive view

Look in the Teradata Message manual. You have a with within a recursive with.
Teradata Employee

Re: Recursive view

Here is an example of a recursive view. Once you create the view you can insert FROM the view to a table.

-- create a test table
CREATE SET TABLE foo (
myname VARCHAR(100),
id integer
)
PRIMARY INDEX ( id );

--insert the values

INSERT 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 view

REPLACE Recursive VIEW MY_LOOKUP (myname, id ) As
(
Select
substr(myname, 1, index(myname,'_')-1) || ' ' || substr(myname, index(myname,'_') +1, character_length(myname)) as myname, id
From foo root
union all
Select
substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.id
From MY_LOOKUP direct
where index(direct.myname,'_') >0)
select * from LOOKUP b
where index(b.myname,'_')=0

--get the data
select * From MY_LOOKUP;

--you can also insert from the view. foo_table must be created!

insert into foo_table select * from my_lookup;
Teradata Employee

Re: Recursive view

CORRECTION

Here is an example of a recursive view. Once you create the view you can insert FROM the view to a table.

-- create a test table
CREATE SET TABLE foo (
myname VARCHAR(100),
id integer
)
PRIMARY INDEX ( id );

--insert the values

INSERT 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 view

REPLACE Recursive VIEW MY_LOOKUP (myname, id ) As
(
Select
substr(myname, 1, index(myname,'_')-1) || ' ' || substr(myname, index(myname,'_') +1, character_length(myname)) as myname, id
From foo root
union all
Select
substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.id
From MY_LOOKUP direct
where index(direct.myname,'_') >0);

--get the data
select * 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 ;
Enthusiast

Re: Recursive view

Hi 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 as
select * from emp_rv;

This is not possible. Could you suggest on this ?? its giving error 6926.
Plz help
Teradata Employee

Re: Recursive view

I 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.
Enthusiast

Re: Recursive view

Thanks Jeff_o.
Joe, any suggestion from your end.
Enthusiast

Re: Recursive view

Is 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 ... )

Enthusiast

Re: Recursive view

Thanks Joe for the reply.
Yes, this can be done. Could you plz provide me an example ??
Enthusiast

Re: Recursive view

It 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)
AS

SELECT E.EMP_ID, E.EMP_NAME, NULL, NULL, J.JOB_CODE, J.JOB_DESC
FROM EMPLOYEE E INNER JOIN JOBTBL J
ON E.JOB_CODE = J.JOB_CODE
AND E.EMP_NAME = 'STEVE'

UNION ALL

SELECT E.EMP_ID, E.EMP_NAME, HR_EMPS.EMPID, HR_EMPS.EMPNAME, J.JOB_CODE, J.JOB_DESC
FROM EMPLOYEE E INNER JOIN HR_EMPS
ON E.MGR_ID = HR_EMPS.EMPID
INNER JOIN JOBTBL J
ON E.JOB_CODE = J.JOB_CODE
;