Archives of the TeradataForum

Message Posted: Thu, 04 Mar 2004 @ 11:20:47 GMT

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

We have several tools which select from views on their own database. This isn't difficult to do and has the benefit that it is possible to change the views for one tool and not for the others if you need to. The way I would do it is:

1) Create a new database SPSS_CLEMENTINE

2) Give this database select with grant access to the databases the views and tables currently exist on

3) Get a list of databases and tables/views you want to create views of by running:

select databasename, tablename
from dbc.tables
where databasename in ('DB1','DB2',etc...)
;

paste the results into a text file tables.txt.

5) Use the magic of awk (or another text processing tool) to create the new view DDL

eg:

create file views.awk containing:

##############################################
BEGIN{
printf("database SPSS_CLEMENTINE;\n\n");
}

{
        printf("create view %s\n",$2);
        printf("as select * from %s.%s;\n\n",$1,$2);
}
#########################################

Run this:

awk -f views.awk tables.txt > createviews.sql

6) Run this in BTEQ

NB. I haven't tested the code above, but I think it's OK. Try it with one create view statement first to make sure it works.

Also, the syntax is for unix, but you can get versions of awk for windows. The file should be the same but the command line may be different.

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