|
|
|
Forum Member
 
Group: Forum Members
Last Login: Yesterday @ 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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
Regards:
Monika
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:00:05 PM
Posts: 72,
Visits: 60
|
|
Look in the Teradata Message manual. You have a with within a recursive with.
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:00:05 PM
Posts: 72,
Visits: 60
|
|
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;
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:00:05 PM
Posts: 72,
Visits: 60
|
|
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 ;
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 4:50:48 AM
Posts: 33,
Visits: 186
|
|
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
Regards:
Monika
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:00:05 PM
Posts: 72,
Visits: 60
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 4:50:48 AM
Posts: 33,
Visits: 186
|
|
Thanks Jeff_o.
Joe, any suggestion from your end.
Regards:
Monika
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
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 ... )
|
|
|
| | |