Terradata : Terradata not taking table-name for query.

Database

Terradata : Terradata not taking table-name for query.

I am working on Terradata SQL query, and in that query I am performing a join. Unfortunately, terradata is not accepting the table name as it is because there is a . or period in table name.

Query :

Insert TEST (NAME) VALUES((
sel
smallname
||' '||bigName
,upper(smallname)
,upper(bigName)
from domain.sourceTable as a
join domain2.destinationtable as b on b.someId=a.otherId))

Error log :

5628: Column smallname not found in domain.a or domain2.b

What am I doing wrong? Any ideas. Thank you. :-)

8 REPLIES
Teradata Employee

Re: Terradata : Terradata not taking table-name for query.

smallname and bigName must be a column name of any table you use in your query.

Are you sure domain.sourceTable or domain2.destinationtable have any column with those names?

Enthusiast

Re: Terradata : Terradata not taking table-name for query.

Check for the column name

Re: Terradata : Terradata not taking table-name for query.

I have removed the 2 more columns which I was trying to get. Modified SQL query is :

Insert TEST (NAME) VALUES((

sel

 smallname||' '||bigName

from domain.sourceTable as a

join  domain2.destinationtable as b on b.someId=a.otherId))

smallname and bigname exist in the DB. But the query processing is going wrong is what I think, why is it trying to look for domain2.smallName instead of domain2.destinationName.smallName is what I don't understand.

Enthusiast

Re: Terradata : Terradata not taking table-name for query.

Its searching in the table...thats the reason the error is

5628: Column smallname not found in domain.a or domain2.b...

In table a or table b, the column name should be present...

Re: Terradata : Terradata not taking table-name for query.

It is. That's what I am saying. It is not printing the correct table name then if it's searching. Why is it printing domain2.smallName instead of domain2.destinationTable.smallName is my only problem. I believe the dot is causing a problem.

Teradata Employee

Re: Terradata : Terradata not taking table-name for query.

As specified the expression says:

select <the result of concatenating the contents of column smallname with a blank with the contents of column bigname>.

It does not say:

select <from a new columname created by concatenating the contents of those columns>

It is not possiblle to create a new columname with an expression in a directly specified SQL text. Only if creating the SQL with dynamic SQL in a stored procedure or by generating the SQL with some program outside.

In either case, smallname and bigname are going to be column references so they would need to exist in the referenced tables in the query.

Teradata Employee

Re: Terradata : Terradata not taking table-name for query.

Are you just saying that the wording of the error message is a bit misleading and it would be clearer to say:

Column smallname not found in a or b

or

Column smallname not found in domain.sourceTable or domain2.destinationtable

since there is actually no table named domain.a or domain2.b?

Re: Terradata : Terradata not taking table-name for query.

That is the normal way of returning error messages when one uses alias for table. Teradata always uses alias name (domain.a and domain.b in this case) while showing the error messages.

Most probably, your column is present with different alias name. Can you share the table structure (SHOW TABLE) for the column (smallname) that you are trying to retrive. 

/Hemant