All Forums Database
Sunil Agrawal 12 posts Joined 07/04
24 Jan 2006
Tablename can be deifned in where clause

I just noticed something in Teradata. We can define a table in the WHERE clause. For example the below query run without any error from the p****r: -select * from personnelwhere personnel1.user_id = 1;Where personnel and personnel1 are two different tables. The result seems to be the cartisan product of table personnel and subquery (select * from personnel1 where user_id = 1) and in the output it displays only the columns of the table defined in the from clause (i.e. table personnel here).Is this a known thing in Teradata. If yes than why it is so. Is there any advantage by this.Regards,Sunil

24 Jan 2006

This is something we should be very careful while writing queries. what happened in the query is personnel1 is considered as an alias for personnel and it works with out error. similarly if we have a query likeselect, d.department_namefrom employee e, department dwhere employee.dept_id = department.dept_idthe above query will work but it will run into issues because of the wrong alias.It is a known thing but we learned it the hard way

hh 21 posts Joined 06/04
24 Jan 2006

I would not say this is bad but just a timesaving feature. To prevent this from happening again, try changing your defaultdatabase to a database contains nothing. The default database is the where Teradata trying to locate the table and if it could not find one, it will fail the query instead.

You must sign in to leave a comment.