Teradata Interval Field Overflow

Interval is the datatype in Teradata which represents duration between 2 time components. We can add INTERVAL to DATE/TIMESTAMP column values to get to new DATE/TIMESTAMP. Also whenever we subtract two timestamp in Teradata, output is of INTERVAL type. Just like any other data type INTERVAL too has permissible range and if the value exceeds it we get “Interval Field Overflow” error. Now you know the real reason of Interval Field Overflow error , let us see few examples which…

Continue Reading

3706: Syntax error: ORDER BY is not allowed in subqueries.

As per SQL standards we cannot use ORDER BY clause in subqueries. It also does not make sense to use ORDER BY inside a derived table.Let’s see an SQL example to identify the problem and how to overcome it. This is a perfectly fine query: SELECT E_EMPNO,E_EMPNAME,E_MOB FROM EMPLOYEE ORDER BY 1,2; Now if we create a derived table using this query it will fail. SELECT COUNT(1) FROM ( SELECT E_EMPNO,E_EMPNAME,E_MOB FROM EMPLOYEE ORDER BY 1,2 )TBL1; Error–> 3706: Syntax…

Continue Reading
Teradata Invalid Date Error

How to find and fix Invalid Date Error in Teradata

Invalid Date is one of the most common date related error we face while loading data from Source to Target. The error is because either the source is not sending date values in format which is same as Target or the date value is not correct. Eg: If Target is expecting date in format ‘YYYY-MM-DD’ then ‘2015-02-31’ is date in format ‘YYYY-MM-DD’ however the value is not correct as February cannot have 31 as date. Other example could be ‘2015/02/25’…

Continue Reading
Table view trigger name is ambiguous

Table/view/trigger name is ambiguous – Not the obvious reason.

In some cases, we write database name explicitly in our queries however most of the time we rely on default database to select the view/table used in queries. However do you know in Teradata , you can have more than one default database for your query. Let’s look at a query below : DATABASE DB_A; SELECT a.col1 FROM table_a a INNER JOIN DB_B.table_b b ON a.col1 = b.col1 INNER JOIN table_c c ON b.col1 = c.col1; The above query will…

Continue Reading
How to free some space in Teradata Database

Target Row Updated by Multiple Source Rows

While working on Teradata, you may have encounter a very common error while using UPDATE command: Error 7547: Target row updated by multiple source rows For any update, in ideal condition, there should be only one row from SOURCE which should match to one or more records in TARGET table however if we are in scenario which is opposite than mentioned before i.e More than one row from SOURCE is matching to one record in TARGET then we will have…

Continue Reading