Teradata gives GLOBAL TEMPORARY option with CREATE TABLE syntax….so, if you want to see the records, you have to explicitly write ON COMMIT PRESERVE ROWS immediately after CREATE TABLE syntax.

1. CREATE GLOBAL TEMPORARY TABLE table_1.
2. (
3. column1 datatype,
4. column2 datatype,
5. .
6. .
7. columnN datatype.
8. ) ON COMMIT PRESERVE ROWS;

To find the time the table last was updated, you can try to parse query log table. For example, let your table name is TNAME and it is located in schema SNAME.

Dummy tables & Charts Dummy tables and charts are empty skeleton tables and charts which show how the results will be presented but which do not contain any data/results.

Teradata classifies the tables as SET or MULTISET tables based on how the duplicate records are handled. A table defined as SET table doesn’t store the duplicate records, whereas the MULTISET table can store duplicate records. ALTER TABLE command is used to add or drop columns from an existing table.

The Teradata RDBMS stores the date in YYYMMDD format on disk. The YYY is an offset value from the base year of 1900. The MM is the month value from 1 to 12 and the DD is the day of the month. Teradata always stores a date as a numeric INTEGER value.

The Teradata DBQL Tables Saves one row per query with the aggregated key figures like the number of CPU seconds, number of IOs, and much more. All SQL text is stored here in the common record for table DBC. DBQLOGTBL. This may require several rows per query.

Find unused tables in Teradata

1. Select DatabaseName,TableName from dbc. tablesv.
2. where DatabaseName = ‘Banking_prod_db’
3. and LastAccessTimeStamp <= CURRENT_DATE -90;

SAP HANA has a table called DUMMY that works the same as the Oracle “dual” table. Teradata database does not require a dummy table. Queries like ‘select 1 + 1’ can be run without a “from” clause/table name.

Testing Modifications on Existing Programs

1. Set up a test environment in the DBMS.
2. Implement the modification in the test environment.
3. Use the copied live data as dummy data.
4. Activate the modification in test.
5. Fix bugs.
6. Go live with the program modification on live data.

Failed [3888 : 42000] A SELECT for a UNION,INTERSECT or MINUS must reference a table. One trick it to use one dummy table: In Teradata, you can only define local variables in macros, procedures and functions.

Date columns are stored as INTEGER internally by Teradata. To calculate integer value for any DATE column in TERADATA, the manner in which it will be stored in TERADATA, try this: Example: If we have a table which has a DATE column DOA and it has some value as ‘2013-03-13’ then we can fetch records like this:

Add months in Teradata You can use add_months function in teradata to add/subtract months from any date value in teradata. In the example below we have fetched current date, added 2 months to current date and subtracted 2 months to current date.

In Teradata, whenever you get YEAR in DATE as a 2 digit number i.e. YY then Teradata has to determine the complete year for that date. Example if the DATE is ’20-01-01′ and format is ‘YY-MM-DD’ then Teradata has to determine whether 20 means 1920 or 2020.