Explanations of the most common Teradata errors
Teradata Database Errors
If you are looking for a detailed explanation of errors that may be raised by Teradata Database, Teradata Database Errors would be your reference.
Teradata ROWNUM and ROW_NUMBER
SELECT
item,
date,
ROW_NUMBER() OVER (ORDER BY date DESC)
FROM items
Teradata jobs
Available Teradata positions
Teradata Corporation:
http://www.teradata.com/careers/
Teradata jobs in the US:
http://jobsearch.monster.com/search/?q=teradata
Teradata jobs in the UK:
http://www.itjobswatch.co.uk/jobs/uk/teradata.do
Teradata jobs worldwide:
http://www.indeed.com/q-Teradata-jobs.html
Teradata Corporation:
http://www.teradata.com/careers/
Teradata jobs in the US:
http://jobsearch.monster.com/search/?q=teradata
Teradata jobs in the UK:
http://www.itjobswatch.co.uk/jobs/uk/teradata.do
Teradata jobs worldwide:
http://www.indeed.com/q-Teradata-jobs.html
Teradata DATE
Teradata ANSI DateTime data types include:
• DATE
• TIME
• TIME WITH TIME ZONE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
The supported DateTime functions are:
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• EXTRACT
Teradata ADD_MONTHS function:
Example 1
This statement returns the current date plus 13 years.
SELECT ADD_MONTHS (CURRENT_DATE, 12*13);
Example 2
This statement returns the date 6 months ago.
SELECT ADD_MONTHS (CURRENT_DATE, -6);
Example 3
This statement returns the current TIMESTAMP plus four months.
SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 4);
Example 4
This statement returns the TIMESTAMP nine months from January 1, 1999. Note the literal form, which includes the keyword TIMESTAMP.
SELECT ADD_MONTHS (TIMESTAMP '1999-01-01 23:59:59', 9);
Example 5
This statement adds one month to January 30, 1999.
SELECT ADD_MONTHS ('1999-01-30', 1);
The result is 1999-02-28.
Example 6
The result of the SELECT statement in this example is a date in February, 1996. The result would be February 31, 1996 if that were a valid date, but because February 31 is not a valid date, ADD_MONTHS normalizes the answer. That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.
SELECT ADD_MONTHS ('1995-12-31', 2);
The result of this example is 1996-02-29. Note that 1996 was a leap year. If the interval were 14 months rather than 2, the result wouldbe '1997-02-28'.
Example 7
This statement performs the converse of the ADD_MONTHS function in “Example 5”. You might expect it to return ‘1999-01-30’, which is the source date in that example, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , -1);
ADD_MONTHS returns the result 1999-01-28. The function performs as designed and this result is not an error, though it might not be what you would expect from reading “Example 5.”
Example 8
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , 1);
ADD_MONTHS returns the result 1999-03-28.
Example 9
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-04-30' , -1);
ADD_MONTHS returns the result 1999-03-30.
Example 10
You might expect the following statement to return '1999-05-31', but it does not.
SELECT ADD_MONTHS ('1999-04-30' , 1);
ADD_MONTHS returns the result 1999-05-30.
Teradata EXTRACT function:
Example 1
The following example returns the year, as an integer, from the current date.
SELECT EXTRACT (YEAR FROM CURRENT_DATE);
Example 2
Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.
SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;
Example 3
The following returns 12 as an integer.
SELECT EXTRACT (DAY FROM '1996-12-12');
Example 4
This example returns an error because the character literal does not evaluate to a valid date.
SELECT EXTRACT (DAY FROM '1996-02-30');
Example 5
The following returns an error because the character string literal does not match the ANSI SQL:2008 date format.
SELECT EXTRACT (DAY FROM '96-02-15');
If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.
Example 6
The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.
SELECT EXTRACT (MINUTE FROM '23:59:17.3');
Example 7
This example returns the hour, as an integer, from the current time.
SELECT EXTRACT (HOUR FROM CURRENT_TIME);
Current time is retrieved as the system value TIME, to the indicated precision.
Example 8
The following example returns the seconds as DECIMAL(8,2). This is based on the fractional
seconds precision of 2 for CURRENT_TIME.
SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));
See more - SQL Functions, Operators, Expressions, and Predicates:
http://www.info.teradata.com/edownload.cfm?itemid=102320046
• DATE
• TIME
• TIME WITH TIME ZONE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
The supported DateTime functions are:
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• EXTRACT
Teradata ADD_MONTHS function:
Example 1
This statement returns the current date plus 13 years.
SELECT ADD_MONTHS (CURRENT_DATE, 12*13);
Example 2
This statement returns the date 6 months ago.
SELECT ADD_MONTHS (CURRENT_DATE, -6);
Example 3
This statement returns the current TIMESTAMP plus four months.
SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 4);
Example 4
This statement returns the TIMESTAMP nine months from January 1, 1999. Note the literal form, which includes the keyword TIMESTAMP.
SELECT ADD_MONTHS (TIMESTAMP '1999-01-01 23:59:59', 9);
Example 5
This statement adds one month to January 30, 1999.
SELECT ADD_MONTHS ('1999-01-30', 1);
The result is 1999-02-28.
Example 6
The result of the SELECT statement in this example is a date in February, 1996. The result would be February 31, 1996 if that were a valid date, but because February 31 is not a valid date, ADD_MONTHS normalizes the answer. That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.
SELECT ADD_MONTHS ('1995-12-31', 2);
The result of this example is 1996-02-29. Note that 1996 was a leap year. If the interval were 14 months rather than 2, the result wouldbe '1997-02-28'.
Example 7
This statement performs the converse of the ADD_MONTHS function in “Example 5”. You might expect it to return ‘1999-01-30’, which is the source date in that example, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , -1);
ADD_MONTHS returns the result 1999-01-28. The function performs as designed and this result is not an error, though it might not be what you would expect from reading “Example 5.”
Example 8
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-02-28' , 1);
ADD_MONTHS returns the result 1999-03-28.
Example 9
You might expect the following statement to return ‘1999-03-31’, but it does not.
SELECT ADD_MONTHS ('1999-04-30' , -1);
ADD_MONTHS returns the result 1999-03-30.
Example 10
You might expect the following statement to return '1999-05-31', but it does not.
SELECT ADD_MONTHS ('1999-04-30' , 1);
ADD_MONTHS returns the result 1999-05-30.
Teradata EXTRACT function:
Example 1
The following example returns the year, as an integer, from the current date.
SELECT EXTRACT (YEAR FROM CURRENT_DATE);
Example 2
Assuming PurchaseDate is a DATE field, this example returns the month of the date value formed by adding 90 days to PurchaseDate as an integer.
SELECT EXTRACT (MONTH FROM PurchaseDate+90) FROM SalesTable;
Example 3
The following returns 12 as an integer.
SELECT EXTRACT (DAY FROM '1996-12-12');
Example 4
This example returns an error because the character literal does not evaluate to a valid date.
SELECT EXTRACT (DAY FROM '1996-02-30');
Example 5
The following returns an error because the character string literal does not match the ANSI SQL:2008 date format.
SELECT EXTRACT (DAY FROM '96-02-15');
If the argument to EXTRACT is a value of type DATE, the value contained is warranted to be a valid date, for which EXTRACT cannot return an error.
Example 6
The following example relates to non-ANSI DateTime definitions. If the argument is a character literal formatted as a time value, it is converted to REAL and processed. In this example, 59 is returned.
SELECT EXTRACT (MINUTE FROM '23:59:17.3');
Example 7
This example returns the hour, as an integer, from the current time.
SELECT EXTRACT (HOUR FROM CURRENT_TIME);
Current time is retrieved as the system value TIME, to the indicated precision.
Example 8
The following example returns the seconds as DECIMAL(8,2). This is based on the fractional
seconds precision of 2 for CURRENT_TIME.
SELECT EXTRACT (SECOND FROM CURRENT_TIME (2));
See more - SQL Functions, Operators, Expressions, and Predicates:
http://www.info.teradata.com/edownload.cfm?itemid=102320046
Download Teradata 13 for free
Download Teradata 13
Teradata 13 Express for VMware Player:
http://downloads.teradata.com/download/database/teradata-express/vmware
Teradata 13 Express for VMware Player:
http://downloads.teradata.com/download/database/teradata-express/vmware
Teradata Index
An index is a physical mechanism used to store and access the rows of a table. Indexes on
tables in a relational database function much like indexes in books, they speed up information
retrieval.
In general, Teradata Database uses indexes to:
• Distribute data rows.
• Locate data rows.
• Improve performance.
• Ensure uniqueness of the index values.
Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022
Teradata Index Wizard:
http://www.info.teradata.com/edownload.cfm?itemid=110590039
tables in a relational database function much like indexes in books, they speed up information
retrieval.
In general, Teradata Database uses indexes to:
• Distribute data rows.
• Locate data rows.
• Improve performance.
• Ensure uniqueness of the index values.
Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022
Teradata Index Wizard:
http://www.info.teradata.com/edownload.cfm?itemid=110590039
What is Teradata
What is Teradata?
Teradata developers designed Teradata Database from mostly off-the-shelf hardware components. The result was an inexpensive, high-quality system that exceeded the performance of conventional relational database management systems. The hardware components of Teradata Database evolved from those of a simple parallel database computer into those of a general-purpose, massively parallel computer running the database. The architecture supports both single-node, Symmetric Multiprocessing (SMP) systems and multinode, Massively Parallel Processing (MPP) systems in which the distributed functions communicate by means of a fast interconnect structure. The interconnect structure is the BYNET for MPP systems and the boardless BYNET for SMP systems.
Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022
Database design:
http://www.info.teradata.com/edownload.cfm?itemid=102320020
Teradata developers designed Teradata Database from mostly off-the-shelf hardware components. The result was an inexpensive, high-quality system that exceeded the performance of conventional relational database management systems. The hardware components of Teradata Database evolved from those of a simple parallel database computer into those of a general-purpose, massively parallel computer running the database. The architecture supports both single-node, Symmetric Multiprocessing (SMP) systems and multinode, Massively Parallel Processing (MPP) systems in which the distributed functions communicate by means of a fast interconnect structure. The interconnect structure is the BYNET for MPP systems and the boardless BYNET for SMP systems.
Introduction to Teradata:
http://www.info.teradata.com/edownload.cfm?itemid=102320022
Database design:
http://www.info.teradata.com/edownload.cfm?itemid=102320020
Teradata FastLoad
Teradata FastLoad is a command-driven utility which can be used to quickly load large amounts of data in an empty table on a Teradata Database.
Teradata FastLoad uses multiple sessions to load data. However, it loads data into only one table on a Teradata Database per job. To load data into more than one table in the Teradata Database, multiple Teradata FastLoad jobs must be submitted—one for each table.
Teradata FastLoad Reference:
http://www.info.teradata.com/edownload.cfm?itemid=110590003
Teradata FastLoad uses multiple sessions to load data. However, it loads data into only one table on a Teradata Database per job. To load data into more than one table in the Teradata Database, multiple Teradata FastLoad jobs must be submitted—one for each table.
Teradata FastLoad Reference:
http://www.info.teradata.com/edownload.cfm?itemid=110590003
Subscribe to:
Posts (Atom)