Can't create a recursive view when specifying the database

Database

Can't create a recursive view when specifying the database

I can create a recursive view like this:

replace recursive view foo (x) as (
select 1 as x from (select 1 as dummy_row) dummy_table
union all
select x+1 from foo where x < 10
);



but not like this:

replace recursive view db.foo (x) as (
select 1 as x from (select 1 as dummy_row) dummy_table
union all
select x+1 from db.foo where x < 10
);



[Error 3807] Object 'db.foo' does not exist.

Any idea why?


1 REPLY
Junior Supporter

Re: Can't create a recursive view when specifying the database

Mark:

Yep. The reference from the 'UNION ALL' part of the query to the (not yet) created view cannot contain the dbname, because the view itself does not exist and it cannot be resolved (data dictionary etc...)

A longer explanation here (in spanish):

http://carlosal.wordpress.com/2010/04/13/vistas-recursivas-en-teradata-el-huevo-o-la-gallina/

HTH.

Cheers.

Carlos.