“An owner referenced by user does not have SELECT WITH GRANT OPTION access to DB_Name.Table_Name.Col_Name “. This is very common error related with privileges granted to objects in Teradata. So first thing you must understand is this error related with insufficient privileges. In this post, we will see how this error can come up while working with views on Teradata. Whenever we create VIEWS in Teradata on top of base Tables, we must grant permission to VIEW database on Table database. This is required because VIEW has to access underlying TABLE and if does not have proper permissions it won’t be able to access it.Read More →

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 explains it. Create a dummy table with some data: create volatile table dummy_ts ( start_ts timestamp , end_ts timestamp ) on commit preserve rows; insertRead More →

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 error: ORDER BY is not allowed in subqueries. Possible Solutions: 1) Now the obvious solution could be to remove ORDER BY clause from the query.Read More →

Teradata Invalid Date Error

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’ is date with not same format as Target however value is correct. Both the error are pretty common. Now let us see how to identifyRead More →

Table view trigger name is ambiguous

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 fail with error: E(3806):Table/view/trigger name ‘table_a’ is ambiguous. This is because table_a exists in DB_A as well as in DB_B. So what exactly is causingRead More →

How to free some space in Teradata Database

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 this error. Let us see by an example: Data in TARGET Table: SEL CUST_ID,CUST_NAME,CUST_CITY FROM CUSTOMERS; CUST_ID CUST_NAME CUST_CITY 1 NITIN CHENNAI 2 RAJ BANGALORERead More →