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)
SELECT Value INTO :Result FROM :Schema.TABLE;
Is something similar supported in Teradata?
Are there any other approaches to flip between test and production tables?
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.
The below query will make the default database to PROD. So whatever tables if we use,it will try to search in PROD Schema.
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.
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.