Does Teradata support database/schema aliases

Database
N/A

Does Teradata support database/schema aliases

I am looking to solve a problem where a procedure located in one schema queries data into one of two other schemas, based on input parameter. Think of it this way - the test tables are in schema TEST, while the production tables are in schema PROD. The behavior I want is:

REPLACE PROCEDURE Sample(IN Schema CHAR(32), OUT Result INTEGER)
BEGIN
SELECT Value INTO :Result FROM :Schema.TABLE;
END;

Is something similar supported in Teradata?

Are there any other approaches to flip between test and production tables?

3 REPLIES

Re: Does Teradata support database/schema aliases

Hi,

Can you please elaborate on the Query? From the understanding it seems that You need to populate data into Schema/DB Prod from Test Schema/Db. If the owner of the Procedure has access to both the PROD & TEST Schemas. He will be able to access data.

There is a keyword called 'DATABASE <<DB_NAME>>;'

The below query will make the default database to TEST. So whatever tables if we use,it will try to  search in TEST Schema.

DATABASE TEST;

The below query will make the default database to PROD. So whatever tables if we use,it will try to  search in PROD Schema.

DATABASE PROD;

Re: Does Teradata support database/schema aliases

You can't pass on database names as the parameter list. The dirty way could be that you can put a check using IF statement and then write two SQLs and fetch the results from TEST or PROD based on the parameter value.

Or alternatively you can build a dynamic SQL using the database name string from the parameter and execute it through DBC.SYSEXEC. But obviously there are limitations for dynamic SQL execution... You need to be aware of that before choosing this option.

N/A

Re: Does Teradata support database/schema aliases

kdimov,

In teradata there is no concept of schema (as understood in other RDBMS) Database.schema.user.object

You can not have two tables under different schemas in the same database.

Regards.